Join Query in SQL

Introduction to Join Query in SQL

In the below section let us see what are joins and different types of joins with examples.

What are Joins?

Joins are used to get data from more than one table. To join more than one table we need at least one column common in both tables. Tables get joined based on the condition specified. “ON” keyword is used. There are different types of joins in SQL as below.

Different Types of Joins

• INNER Join
• LEFT Join
• RIGHT Join
• FULL Join

1. INNER Join

Inner join gets all the rows that are common in both tables based on the condition specified. Let us take an example of the inner join.

Example:

Below represents the Venn diagram of the inner join.

Here let’s consider table A and table B. As Inner join considers the common rows of both the tables. The shaded area represents the common rows of both table.

Syntax:

SELECT * FROM TABLE_A A
INNER JOIN TABLE_B B
ON A. Common_COLUMN =B. Common_COLUMN

2. LEFT Join

Left Join gets all the rows from the Left table and common rows of the both table. Let us take an example of the left join.

Example:

Below represents the Venn diagram of the left join.

In below diagram Table A is left join to the table B. Here all the rows from the table A gets considered and common rows from both table.

Syntax:

SELECT * FROM TABLE_A A
LEFT JOIN TABLE_B B
ON A. Common_COLUMN =B. Common_COLUMN

3. RIGHT Join

Right Join gets all the rows from the Right table and common rows of the both table. Let us take an example of the right join.

Example:

Below represents the Venn diagram of the right join.

In below diagram Table A is right join to the table B. Here all the rows from the table B gets considered and common rows from both table.

Syntax:

SELECT * FROM TABLE_A A
RIGHT JOIN TABLE_B B
ON A.Common_COLUMN=B.Common_COLUMN

4. FULL Join

Full Join gets all the rows from the both tables. Let us take an example of the right join.

Example:

Below represents the Venn diagram of the FULL join.

Syntax:

SELECT * FROM TABLE_A A
FULL JOIN TABLE_B B
ON A. Common_COLUMN =B. Common_COLUMN

The result set contains NULL set values. Below syntax can be used to neglect the NULL values: –

SELECT * FROM TABLE_A A
FULL JOIN TABLE B B
ON A. Common_COLUMN =B. Common_COLUMN
WHERE A.Common_COLUMN IS NULL
AND A.Common_COLUMN IS NULL

How to Use Join Query in SQL with Examples

Here we discuss the uses of join query with examples:

1. Left Join

Left Join = All rows from left table + INNER Join

Example:

Let us consider two tables and apply Left join on the tables: –

Loan Table:

Loan_no	Loan_status	State	Loan_Amount	Ap_Date
12	Open	Ranchi	30000	01-02-2015
23	Close	Patna	50000	03-04-2017
31	Pending	Kolkata	80000	09-07-2018
43	Approval	Electronic city	54000	10-11-2019
11	Rejected	Bangalore	43000	03-04-2017
33	Close	Patna	90000	09-07-2018
44	Open	Kolkata	67000	01-01-2020

Borrower Table:

Loan_no	Borrower_Name	Borrower_Date	Bank_id
12	Ram	09-07-2014	A1
27	Sundar	19-06-2016	A2
43	Browny	16-07-2019	A4
31	Blacky	09-07-2015	A2

Query to get the loan_no, status and borrower date from two tables: –

Query:

SELECT L.LOAN_NO, L.LOAN_STATUS,B.BORROWER_DATE
FROM LOAN L LEFT JOIN BORROWER B
ON L.LOAN_NO=B.LOAN_NO

Let’s check the output of the above table after applying the Left join on them.

Output:

2. RIGHT Join

RIGHT Join = All rows from RIGHT table + INNER Join

Example:

Let us consider two tables and apply RIGHT join on the tables: –

Query to get the loan_no, status and borrower date from two tables: –

Query: 

SELECT L.LOAN_NO, L.LOAN_STATUS,B.BORROWER_DATE
FROM LOAN L RIGHT JOIN BORROWER B
ON L.LOAN_NO=B.LOAN_NO

Let’s check the output of the above table after applying the right join on them.

Output:

3. INNER Join

Inner Join = All common rows from both table. While joining at least one column should be of same data type and common among tables.

Example:

Let us consider two tables and apply INNER join on the tables: –

Let us build a query to get the loan_no, status and borrower date from two tables: –

Query:

SELECT L.LOAN_NO,L.LOAN_STATUS,B.BORROWER_DATE
FROM LOAN L INNER JOIN BORROWER B
ON L.LOAN_NO=B.LOAN_NO

Let’s check the output of the above table after applying the inner join on them.

Output:

Here in the above output we got the common rows of both table based on the condition “L.LOAN_NO=B.LOAN_NO”.

4. FULL OUTER Join

FULL OUTER Join = All rows from both table. While joining at least one column should be of same data type and common among tables.

Example:

Let us consider two tables and apply FULL OUTER join on the tables: –

Let us build a query to get the ap_date and borrower date from two tables: –

Query: 

SELECT L.LOAN_NO,L.LOAN_STATUS,B.BORROWER_DATE
FROM LOAN L FULL OUTER JOIN BORROWER B
ON L.LOAN_NO=B.LOAN_NO

Let’s check the output of the above table after applying the inner join on them.

Output:

Here in the above output we got the common rows of both table based on the condition “L.LOAN_NO=B.LOAN_NO”.

Conclusion

To fetch data relevant to the customer requirement we might need to join tables which will be fulfilled by joins. As mentioned earlier joins are used to get data from more than one table. To join more than one table we need at least one column common in both tables. Tables get joined based on the condition specified.

Recommended Articles

This is a guide to Join Query in SQL. Here we also discuss the introduction and different types of joins along with different examples and its code implementation. You may also have a look at the following articles to learn more –

1. SQL DATEADD()
2. SQL Rename Table
3. SQL NULLIF()
4. MySQL ROUND