SQL Joins
SQL Joins:
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:
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:
