SQL Join's Example

'Join' by word itself we get to know, it talks about something we combine together to get desired result.

In database terms, join is all about combining data from multiple tables as single record. Now days, all the data in database is normalized and to display some meaning full information we need to combine data from more than one table.

For example, let's take classic example of customer and order.
In real world, customer information will go in CUSTOMERS table and orders will go in ORDERS table having FOREIGN_KEY for CUSTOMER_ID.
Now when we have to retrieve data for one of use case say, who has placed order with ID=xyz. Here we will get the record from ORDERS, and check for respective customer from CUSTOMERS table using FOREIGN_KEY in that record, combine this data and result will be populated.
Now such operations can be done either by coding or SQL way.

Lets now talk more on SQL Join's in detail -

While talking about SQL Join's, there are 4 basic types of join's as -

1. Inner
2. Left
3. Right
4. Full

To understand this type quickly let's see below examples with diagrams -

Considering above diagram and join scenario's we might need data like -

1. Only B              [Inner]
2. A and B            [Left]
3. B and C            [Right]
4. All A, B and C    [Full]

Lets see in detail one by one -

Before going to types, see below database table example, which will be used as reference further to explain all types of Join's.

CUSTOMERS

CUST_ID	CUST_NAME	CUST_CONTACT	CUST_CITY
11111	Ram	32145646	Pune
11112	Sarika	65465812	Mumbai
11113	Manisha	45454545	Delhi
11114	Vishal	89295956	Chennai
11115	Mahesh	54945956	Jaipur

ORDERS

ORDER_ID	O_DATE	AMOUNT	CUST_ID
111	22-05-2017	1200	11111
112	23-05-2017	3500	11112
113	24-05-2017	200	11114
114	24-05-2017	2000	11120

Inner Join

[Select all records from MyTable1 and MyTable2, for which the given join condition is valid]

Let's say we want to display list of customers whole had made an order, along with the details of order they placed.
If we consider above diagram, this use case will be, all the records from MyTable1 for which there is reference in MyTable2 (Portion B).
Best fit example for Inner Join.

Query:

SELECT CUST_NAME, CUST_CONTACT, O_DATE, AMOUNT FROM CUSTOMERS C INNER JOIN ORDERS O ON C.CUST_ID = O.CUST_ID;

INNER JOIN RESULT

CUST_NAME	CUST_CONTACT	O_DATE	AMOUNT
Ram	32145646	22-05-2017	1200
Sarika	65465812	23-05-2017	3500
Vishal	89295956	24-05-2017	200

Left Join

[Select all records from MyTable1 along with records from MyTable2, for which the given join condition is valid]

Simply we want to display all the customers and their respective orders.
i.e. All the records from MyTable1 (A) for which there is reference in MyTable2 (B) and NOT records from MyTable2 (C) for which there is no entry in MyTable1.
This is good example for Left Join.

Query:

SELECT CUST_NAME, CUST_CONTACT, O_DATE, AMOUNT FROM CUSTOMERS C LEFT JOIN ORDERS O ON C.CUST_ID = O.CUST_ID;

LEFT JOIN RESULT

CUST_NAME	CUST_CONTACT	O_DATE	AMOUNT
Ram	32145646	22-05-2017	1200
Sarika	65465812	23-05-2017	3500
Vishal	89295956	24-05-2017	200
Manisha	45454545	NULL	NULL
Mahesh	54945956	NULL	NULL

Right Join

[Select all records from MyTable2 along with records from MyTable1, for which the given join condition is valid] 
Now consider that, we have to display all the order details along with customers who had placed the order.
i.e. All the records from MyTable2 (C), for which there is reference in MyTable1 (B), and NOT there records from MyTable1 (A) which don't have corresponding record in MyTable2.

Query:


SELECT CUST_NAME, CUST_CONTACT, O_DATE, AMOUNT FROM CUSTOMERS C RIGHT JOIN ORDERS O ON C.CUST_ID = O.CUST_ID;

RIGHT JOIN RESULT

CUST_NAME	CUST_CONTACT	O_DATE	AMOUNT
Ram	32145646	22-05-2017	1200
Sarika	65465812	23-05-2017	3500
Vishal	89295956	24-05-2017	200
NULL	NULL	24-05-2017	2000

Full Join

[Select all records from MyTable1 along with records from MyTable2, regardless of given join condition is valid or not]

If we have to display all the details of customer tables as well of order tables.

Query:

SELECT CUST_NAME, CUST_CONTACT, O_DATE, AMOUNT FROM CUSTOMERS C FULL JOIN ORDERS O ON C.CUST_ID = O.CUST_ID;

FULL JOIN RESULT

CUST_NAME	CUST_CONTACT	O_DATE	AMOUNT
Ram	32145646	22-05-2017	1200
Sarika	65465812	23-05-2017	3500
Vishal	89295956	24-05-2017	200
NULL	NULL	24-05-2017	2000
Manisha	45454545	NULL	NULL
Mahesh	54945956	NULL	NULL