The JOIN
keyword is used in an SQL statement to query data from two or more tables,
based on a relationship between certain columns in these tables.
Tables in a
database are often related to each other with keys.
A primary
key is a column (or a combination of columns) with a unique value for each row.
Each primary key value must be unique within the table. The purpose is to bind
data together, across tables, without repeating all of the data in every table.
Look at the
"Persons" table:
P_Id
|
LastName
|
FirstName
|
Address
|
City
|
1
|
Hansen
|
Ola
|
Timoteivn 10
|
Sandnes
|
2
|
Svendson
|
Tove
|
Borgvn 23
|
Sandnes
|
3
|
Pettersen
|
Kari
|
Storgt 20
|
Stavanger
|
Note that
the "P_Id" column is the primary key in the "Persons"
table. This means that no two rows can have the same P_Id. The P_Id
distinguishes two persons even if they have the same name.
Next, we
have the "Orders" table:
O_Id
|
OrderNo
|
P_Id
|
1
|
77895
|
3
|
2
|
44678
|
3
|
3
|
22456
|
1
|
4
|
24562
|
1
|
5
|
34764
|
15
|
Next, we
have the "Orders" table:
Note that
the "O_Id" column is the primary key in the "Orders" table
and that the "P_Id" column refers to the persons in the
"Persons" table without using their names.
Notice that
the relationship between the two tables above is the "P_Id" column.
Different SQL JOINs
Before we
continue with examples, we will list the types of JOIN you can use, and the
differences between them.
JOIN: Return
rows when there is at least one match in both tables
LEFT JOIN:
Return all rows from the left table, even if there are no matches in the right
table
RIGHT JOIN:
Return all rows from the right table, even if there are no matches in the left
table
FULL JOIN:
Return rows when there is a match in one of the tables
SQL INNER
JOIN
Description
The INNER JOIN
will select all rows from both participating tables as long as there is a match
between the columns. An SQL INNER JOIN is same as JOIN clause, combining rows
from two or more tables.
Syntax
Select *
FROM table1
INNER JOIN
table2
ON
table1.column_name = table2.column_name;
OR
Select *
FROM table1
JOIN table2
ON
table1.column_name = table2.column_name;
Parameters
Name
|
Description
|
table1, table2
|
Name of the tables participating in
joining.
|
column_name
|
Key column of the participating
tables.
|
Pictorial representation of Sql INNER
JOIN
The INNER
JOIN in SQL joins two tables according to the matching of a certain criteria
using a comparison operator.
Example of
Sql INNER JOIN
Sample table
: foods
item_id
|
item_name
|
item_unit
|
company_id
|
1
|
Chex Mix
|
Pcs
|
16
|
6
|
Cheez-lt
|
Pcs
|
15
|
2
|
BN Biscuit
|
Pcs
|
15
|
3
|
Mighty Munch
|
Pcs
|
17
|
4
|
Pot Rice
|
Pcs
|
15
|
5
|
Jaffa Cakes
|
Pcs
|
18
|
7
|
Salt n Shake
|
Pcs
|
NULL
|
Sample table
: company
company_id
|
company_name
|
company_city
|
18
|
Order All
|
Boston
|
15
|
Jack Hill Ltd
|
London
|
16
|
Akas Foods
|
Delhi
|
17
|
Foodies.
|
London
|
19
|
sip-n-Bite.
|
New York
|
To join
'item_name', 'item_unit' columns from 'foods' table and 'company_name',
'company_city' columns from 'company' table, with following condition -
1.
'company_id' of 'foods' and 'company' table must be same,
the following sql statement can be
used :
SELECT
foods.item_name,foods.item_unit,
company.company_name,company.company_city
FROM
foods
INNER JOIN
company
ON foods.company_id
=company.company_id;
Output of Sql INNER JOIN
SQL LEFT JOIN
Description
The SQL LEFT
JOIN, joins two tables and fetches rows based on a condition, which are
matching in both the tables, and the unmatched rows will also be available from
the table before the JOIN clause.
Syntax
Select *
FROM table1
LEFT OUTER
JOIN table2
ON
table1.column_name=table2.column_name ;
Parameters
Name
|
Description
|
table1, table2
|
Name of the tables participating in
joining.
|
column_name
|
Column of the participating tables.
|
Pictorial
representation of Sql LEFT JOIN
SQL LEFT
join fetches a complete set of records from table1, with the matching records
(depending upon the availability) in table2. The result is NULL in the right
side when no matching will take place.
SQL left
join or left outer join : Example
Sample table
: foods
item_id item_name item_unit company_id
|
1 Chex
Mix Pcs 16
|
6 Cheez-lt Pcs 15
|
2 BN
Biscuit Pcs 15
|
3 Mighty
Munch Pcs 17
|
4 Pot
Rice Pcs 15
|
Sample table
: company
company_id
|
company_name
|
company_city
|
18
|
Order All
|
Boston
|
15
|
Jack Hill Ltd
|
London
|
16
|
Akas Foods
|
Delhi
|
17
|
Foodies.
|
London
|
19
|
sip-n-Bite.
|
New York
|
To get
'company_name' and 'company_id' columns from 'company' table and 'company_id',
'item_name', 'item_unit' columns from 'foods' table, after an OUTER JOINING
with these mentioned tables, the following SQL statement can be used :
SELECT
company.company_id,company.company_name,
company.company_city,foods.company_id,foods.item_name
FROM company
LEFT OUTER
JOIN foods
ON
company.company_id = foods.company_id;
Output of
SQL left join or left outer join
SQL RIGHT JOIN
Description
The SQL
RIGHT JOIN, joins two tables and fetches rows based on a condition, which are
matching in both the tables, and the unmatched rows will also be available from
the table written after the JOIN clause.
Syntax : SQL
Right Join
Select *
FROM table1
RIGHT OUTER
JOIN table2
ON
table1.column_name=table2.column_name ;
Parameters
Name
|
Description
|
table1, table2
|
Name of the tables participating in
joining.
|
column_name
|
Column of the participating tables.
|
Pictorial
representation of Sql RIGHT JOIN
SQL RIGHT
join fetches a complete set of records from table2, i.e. the rightmost table
after JOIN clause, with the matching records (depending upon the availability)
in table1. The result is NULL in the left side when no matching will take
place.
SQL Right
join or right outer join : Example
Sample table
: foods
item_id
|
item_name
|
item_unit
|
company_id
|
1
|
Chex Mix
|
Pcs
|
16
|
6
|
Cheez-lt
|
Pcs
|
15
|
2
|
BN Biscuit
|
Pcs
|
15
|
3
|
Mighty Munch
|
Pcs
|
17
|
4
|
Pot Rice
|
Pcs
|
15
|
5
|
Jaffa Cakes
|
Pcs
|
18
|
7
|
Salt n Shake
|
Pcs
|
NULL
|
Sample table
: company
company_id
|
company_name
|
company_city
|
18
|
Order All
|
Boston
|
15
|
Jack Hill Ltd
|
London
|
16
|
Akas Foods
|
Delhi
|
17
|
Foodies.
|
London
|
19
|
sip-n-Bite.
|
New York
|
To get
'company_id', 'company_name' and 'company_city' columns from 'company' table
and 'company_id', 'item_name' columns from 'foods' table, after an OUTER
JOINING with these mentioned tables, the following SQL statement can be used :
SELECT
company.company_id,company.company_name,
company.company_city,foods.company_id,foods.item_name
FROM company
RIGHT OUTER
JOIN foods
ON
company.company_id = foods.company_id;
Output of
SQL right join or right outer join
SQL FULL OUTER JOIN
The FULL
OUTER JOIN will return all rows, as long as there's matching data in one of the
tables. It includes all the rows from both the participating tables and does
not select either the LEFT or RIGHT table from the JOIN key word.
The FULL
OUTER JOIN combines the results of both left and right outer joins. When no
matching rows exist for rows on the left side of the JOIN key word, NULL values
will be returned from the result set on the right. On the other hand , when no
matching rows exist for rows on the right side of the JOIN key word, NULL
values will be returned from the result set on the left.
Sql Full
Outer Join : Syntax
Select *
FROM table1
FULL OUTER
JOIN table2
ON
table1.column_name=table2.column_name ;
Parameters
Name
|
Description
|
table1, table2
|
Name of the tables participating in
joining.
|
column_name
|
Column of the participating tables
|
Pictorial
representation of Sql Full Outer Join
In SQL, FULL
OUTER JOIN returns all rows from both the tables, ignoring the row(s) of one
table is matching with the row(s) of another table.
Sql Full
Outer Join : Example
Sample table
: foods
item_id
|
item_name
|
item_unit
|
company_id
|
1
|
Chex Mix
|
Pcs
|
16
|
6
|
Cheez-lt
|
Pcs
|
15
|
2
|
BN Biscuit
|
Pcs
|
15
|
3
|
Mighty Munch
|
Pcs
|
17
|
4
|
Pot Rice
|
Pcs
|
15
|
Sample table
: company
company_id
|
company_name
|
company_city
|
18
|
Order All
|
Boston
|
15
|
Jack Hill Ltd
|
London
|
16
|
Akas Foods
|
Delhi
|
17
|
Foodies.
|
London
|
19
|
sip-n-Bite.
|
New York
|
To get
'company_id', 'company_name' and 'company_city' columns from 'company' table
and 'company_id', 'item_name' columns from 'foods' table, after a FULL OUTER
JOINING with these mentioned tables, the following sql statement can be used :
SELECT
company.company_id,company.company_name,
company.company_city,foods.company_id,foods.item_name
FROM company
FULL OUTER
JOIN foods
ON
company.company_id = foods.company_id;
SQL CROSS JOIN
Description
The SQL
CROSS JOIN produces a result set which is the number of rows in the first table
multiplied by the number of rows in the second table, if no WHERE clause is
used along with CROSS JOIN. This kind of result is called as Cartesian Product.
If, WHERE clause
is used with CROSS JOIN, it functions like an INNER JOIN.
An
alternative way of achieving the same result is to use column names separated
by commas after SELECT and mentioning the table names involved, after a FROM
clause.
Syntax
Select *
FROM table1
CROSS JOIN
table2;
Pictorial representation of Cross Join syntax
Example of
Sql cross join
Sample table
: foods
item_id
|
item_name
|
item_unit
|
company_id
|
1
|
Chex Mix
|
Pcs
|
16
|
6
|
Cheez-lt
|
Pcs
|
15
|
2
|
BN Biscuit
|
Pcs
|
15
|
3
|
Mighty Munch
|
Pcs
|
17
|
4
|
Pot Rice
|
Pcs
|
15
|
5
|
Jaffa Cakes
|
Pcs
|
18
|
7
|
Salt n Shake
|
Pcs
|
NULL
|
Sample table
: company
company_id
|
company_name
|
company_city
|
18
|
Order All
|
Boston
|
15
|
Jack Hill Ltd
|
London
|
16
|
Akas Foods
|
Delhi
|
17
|
Foodies.
|
London
|
19
|
sip-n-Bite.
|
New York
|
To get
'item_name' and 'item_unit' columns from 'foods' table and 'company_name',
'company_city' columns from 'company' table, after a CROSS JOINING with these
mentioned tables, the following sql statement can be used :
SELECT
foods.item_name,foods.item_unit,
company.company_name,company.company_city
FROM
foods
CROSS JOIN
company;
or
SELECT
foods.item_name,foods.item_unit,
company.company_name,company.company_city
FROM
foods,company;
How cross joining happend into two tables
