SQL JOIN

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

Description

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