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:

Borrower Table:

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

Popular Course in this category

JDBC Training (6 Courses, 7+ Projects)
  6 Online Courses |  7 Hands-on Projects|  37+ Hours|  Verifiable Certificate of Completion
4.5

Course Price

View Course

PHP Training (5 Courses, 3 Project)

4.9

Windows 10 Training (4 Courses, 4+ Projects)

4.8

SQL Training Program (7 Courses, 8+ Projects)

4.7

PL SQL Training (4 Courses, 2+ Projects)

4.7

Oracle Training (14 Courses, 8+ Projects)

4.7

---

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