How To Join Two Different Tables In Sql

SQL Join Two Tables

Introduction to SQL Join Two Tables

Let's discuss about joining two tables along with the syntax and examples. Let us consider the Loan table and Borrower table and apply all types of joins such as the below types. The different types of joins which we are going to apply for the tables are as below:

Different types of Joins

INNER Join
LEFT Join
RIGHT Join
FULL Join

a) 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.

Syntax:

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

b) LEFT JOIN: Left Join gets all the rows from the Left table and common rows of both tables. Let us take an example of the left join.

Syntax:

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

c) RIGHT JOIN: Right Join gets all the rows from the Right table and common rows of both tables. Let us take an example of the right join.

Syntax:

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

d) FULL JOIN: Full Join gets all the rows from both tables. Let us take an example of the right 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 Join Two Tables?

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:

SQL Join Two Tables example 1.1

Borrower:

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.

Below is the screenshot:

SQL Join Two Tables example 1.3

2. RIGHT Join

RIGHT Join = All rows from RIGHT table + INNER Join
Consider all rows from the right table and common from both tables.
Joins based on a condition
ON keyword is used to specify the condition and join the tables.

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:

Below is the screenshot:

example 2

3. INNER Join

Inner Join = All common rows from both tables. While joining at least one column should be of the 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.

Here in the above output, we got the common rows of both tables based on the condition "L.LOAN_NO=B.LOAN_NO".

Screenshot below:

example 3

4. FULL OUTER Join

FULL OUTER Join = All rows from both tables. While joining at least one column should be of the 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,,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:

Screenshot below:

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.