Joins in SQL with Examples

SQL:   SQL means Structured Query Language.SQL is Primarily use for getting information
into and out of Database. It is communicate with database. It is used to deal with relational database. That means we can querying and editing information in certain database.

Database:

Database is a computer software program is used to store,edit and retrieve data. some of the common databases are Mysql,Oracle etc...

Join in SQL: 

SQL Join is used to retrieve data from  more than one table. We can Write the join condition in the where clause. Joins are used for combining the column from  more than one table by using values common to both tables. For joining two or more tables in SQL Queries we used a keyword as "join" . In my previous i have written  Stored Procedure in SQL Server go through it to understand and learn about it.


Types of Joins: 

There are FIVE types of Joins in SQL. They are as follows

1) Cartesian product or Cross Join
2) Inner Join
3) Outer Join
4) Left Join
5) Right Join

1)Cross Join or Cartesian Product:

Cartesian Product is formed when Join condition is Omitted. To avoid a Cartesian Product ,always include a valid condition in a where clause. Here First Table Employee Rows completely Multiplied by First Row in the Second table Address and so on...

I am going to demonstrate this concept with example using Oracle 10g application using Customers table and Orders Table as shown below.


Customers Table:

create table customers
(
customer_id int,
First_Name varchar2(20),
Last_Name varchar2(20),
address varchar2(20),
city varchar2(20),
state varchar2(20));

Result:

Orders Table:

create table orders
(
order_id int,
amount number,
customer_id int
);

Result:

Syntax for Cross-join:

Select Column-name-list
from table-name1
cross JOIN
table-name2


 The Cartesian Product query will be as follows:

select * from customers
cross JOIN orders;

The Output table will be Like This:

Note:  Here we get No of  Rows * No Columns as Output.

2)Inner Join or Equi Join:

The Relationship Between Employee Table and Address Table is a EQUI-JOIN. That is values in Address table Emp ID and Employee table Emp ID both tables are EQUL.

Syntax for Inner Join:

SELECT column-name-list
from table-name1 
INNER JOIN 
table-name2
on table-name1.column-name = table-name2.column-name;

The Query will Look Like This:

select first_name,last_name,order_id,amount 
from customers c
inner join orders o
on c.customer_id=o.customer_id;

The Output will be as follows:

Outer Join:

Outer Join is used both for Matched and Unmatched data.This is again divided into

•  Right Join (or)Right Outer Join
•  Left Join  (or) Left Outer Join

Left Outer Join:

Left Join Performs a Join Starting with a  first table that is (Left most) and then any matching Second(right most)table records.

Syntax for Left Outer Join

SELECT column-name-list
from table-name1 
LEFT OUTER JOIN table-name2
on table-name1.column-name = table-name2.column-name;

The Query for Left Join:
select first_name,last_name,order_id,amount 
from customers c
left join orders o
on c.customer_id=o.customer_id;

The Output will be Like This:

Right Outer Join:

Left Join Performs a Join Starting with a  Second table that is (Right most) and then any matching First (Left most)table records.

                











Syntax for Right Outer Join

SELECT column-name-list
from table-name1 
RIGHT  OUTER JOIN table-name2
on table-name1.column-name = table-name2.column-name;


The Query For Right Join:
select first_name,last_name,order_id,amount 
from customers c
right join orders o
on c.customer_id=o.customer_id;

The Output will be Like This:

Also read this post: HR Interview Questions for Experienced Candidate

I hope you people enjoy this post and share on social websites and give your comments on this topic.Keep follow me for my latest updates