opencodez

SQL for QA: Tutorial#9 – JOIN Clause

Supriya

JOIN clause is used to combine data from two or more tables, after joining it comes as a single set of data and we can call it Result set. Tables are combined using JOIN clause by using COMMON columns from both tables.

We will see different types of JOINs in SQL:

1. INNER JOIN:  This type of Join returns records that have matching values in both tables.

2. LEFT OUTER JOIN: This type of Join returns all records from the left table, and the matched records from
the right table.

3. RIGHT OUTER JOIN: This type of Join returns all records from the right table, and the matched records
from the left table.

4. FULL OUTER  JOIN: This type of Join returns all records when there is a match in either left or right table.

We will consider Order and Customer table as a demo table to demonstrate all Join operations.

Table# 1: Customer

C_ID | C_Name | C_Address | C_Email            | C_Phone

-----+--------+-----------+--------------------+---------

1    | John   | Pune      | John12@yahoo.com   |   12234

2    | Mike   | Goa       | Mike21@gmail.com   |  232234

3    | Anna   | Mumbai    | Anna5@gmail.com    |  892234

4    | David  | Pune      | David31@yahoo.com  | 5692234

5    | Peter  | Goa       | Peter7@hotmail.com | 8989890

Table#2 :  Order

O_ID | C_ID | O_Date     |  O_Amount

-----+------+------------+--------

101  |  1   | 2019-02-16 |  20000

102  |  2   | 2019-05-11 |  50000

103  |  1   | 2019-06-19 |  40000

104  |  3   | 2019-07-29 |  10000

105  |  4   | 2019-08-26 |  70000

106  |  10  | 2019-01-12 |   8000

107  |  19  | 2019-01-01 |  89000

INNER JOIN:

Suppose there are 2 tables Table1 and Table2, Inner join combines all rows from both tables as per the given condition. It selects all records from Table1 and Table2, where the join condition met (as per the common column in both tables) and creates new result table.

Syntax:

SELECT Column_Name(s)
FROM xTable1x
INNER JOIN xTable2x
ON xTable1x.Column_Name = xTable2x.Column_Name;

Example:

SELECT Order.O_ID, Customer.C_Name, Order.O_Date FROM Order 
INNER JOIN Customer 
ON Order.C_ID=Customer.C_ID;

OUTPUT:

O_ID  | C_Name | O_Date

------+--------+------------

101   | John   | 2019-02-16

102   | Mike   | 2019-05-11

103   | John   | 2019-06-19

104   | Anna   | 2019-07-29

105   | David  | 2019-08-26

In above query Inner join concept is used, which created new result set by combining columns of Customer and Order table, in this query each row of First table get compared with each row of Second table and displays all possible rows which satisfy the join predicate .

LEFT OUTER JOIN

The LEFT OUTER JOIN keyword creates a result set which returns all records from the LEFT table (xTable1x), and the matched records from the RIGHT table (xTable2x). The result set shows NULL value from the right side, if there is no match found.

SELECT Column_Name(s)
FROM xTable1x
LEFT JOIN xTable2x
ON xTable1x.Column_Name = xTable2x.Column_Name;

Example:

SELECT Customer.C_Name,Order.O_Date, Order.O_Amount 
FROM Customer 
LEFT OUTER JOIN Order 
ON Customer.C_ID=Order.C_ID;

OUTPUT:

 C_Name | O_Date     |  O_Amount

--------+------------+--------

 John   | 2019-02-16 |  20000

 Mike   | 2019-05-11 |  50000

 John   | 2019-06-19 |  40000

 Anna   | 2019-07-29 |  10000

 David  | 2019-08-26 |  70000

 Peter  |            |

In above result all the customers displayed from Customer table which is LEFT JOIN with Order table, for one record there is no match found in Order table so it displays NULL values.

RIGHT OUTER JOIN

The RIGHT OUTER JOIN keyword creates a result set which returns all records from the RIGHT table (xTable2x), and the matched records from the LEFT table (xTable1x). The result set shows NULL value, if there is no matching row found on left side table.

SELECT Column_Name(s)
FROM xTable1x
RIGHT  JOIN xTable2x
ON xTable1x.Column_Name = xTable2x.Column_Name;

Example:

SELECT Order.O_ID, Order.O_Amount, Customer.C_Name, Customer.C_Phone
FROM Customer 
RIGHT OUTER JOIN Order 
ON Customer.C_ID=Order.C_ID;

OUTPUT:

O_ID | O_Amount | C_Name | C_Phone

-----+----------+--------+---------

101  |  20000   | John   |  12234

102  |  50000   | Mike   |  232234

103  |  40000   | John   |  12234

104  |  10000   | Anna   |  892234

105  |  70000   | David  |  5692234

106  |  8000    |        |

107  |  89000   |        |

Above query  returns all records from the right table (Order) and matches it with left table (Customer) , if there are no matches in the left table it will display null in result set.

 FULL OUTER JOIN

FULL JOIN operation creates the result-set and combines result of both LEFT JOIN and RIGHT JOIN operation.

The final result-set contains all the rows from both tables. The rows for which there is no match found, the result-set will contain NULL values.

Syntax:

SELECT Column_Name(s)
FROM xTable1x
FULL OUTER JOIN xTable2x
ON xTable1x.Column_Name = xTable2x.Column_Name
WHERE condition;

Example:

SELECT Order.O_ID, Customer.C_ID, Customer. C_Email 
FROM Customer 
FULL JOIN Order 
ON Customer.C_ID=Order.C_ID;

OUTPUT:

O_ID | C_ID |  C_Email

-----+------+------------------

101  |  1   | John12@yahoo.com

102  |  2   | Mike21@gmail.com

103  |  1   | John12@yahoo.com

104  |  3   | Anna5@gmail.com

105  |  4   | David31@yahoo.com

106  |      |

107  |      |

     |  5   | Peter7@hotmail.com

In above query FULL OUTER JOIN operation combines all the rows from both the tables and wherever there is no match found it shows null values.

This is all about SQL Joins , we can use joins with Aggregate functions and with other clauses too like UPDATE, DELETE, INSERT, SELECT, GROUP BY etc.

SQL joins also combines 2 or more tables then what is the difference between these two, let’s see some differences.

                           JOIN                         UNION
JOIN combines data of 2 or more tables into new columns UNION combines data into new rows
JOIN columns are combined into result set UNION rows are combined into result set.

 
For JOIN operation, number of columns should NOT be same in both the Select statement For UNION operation, number of columns should be same in both the Select statement
The types of JOINS are Inner, Outer, Left, Right The types of UNION are Union and Union All

 Please let us know if you need to know more.

Tutorial Index:

  1. SQL For QA : Tutorial#1 – Select Query
  2. SQL For QA : Tutorial#2 – Where Clause
  3. SQL for QA: Tutorial#3 – INSERT INTO Statement
  4. SQL for QA: Tutorial#4 – UPDATE Statement
  5. SQL for QA: Tutorial#5 – DELETE Statement
  6. SQL for QA: Tutorial#6 – ORDER BY Clause
  7. SQL for QA: Tutorial#7 – GROUP BY Clause
  8. SQL for QA: Tutorial#8 – UNION Clause
  9. SQL for QA: Tutorial#9 – JOIN Clause

x