SQL for QA: Tutorial#9 – JOIN Clause

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

Table#2 :  Order

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:

Example:

OUTPUT:

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 (<Table1>), and the matched records from the RIGHT table (<Table2>). The result set shows NULL value from the right side, if there is no match found.

Example:

OUTPUT:

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 (<Table2>), and the matched records from the LEFT table (<Table1>). The result set shows NULL value, if there is no matching row found on left side table.

Example:

OUTPUT:

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:

Example:

OUTPUT:

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