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:
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:
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:
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:
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.
Recommended Articles
This is a guide to SQL Join Two Tables. Here we discuss the different types of joins which we are going to apply for the tables along with the examples. You may also have a look at the following articles to learn more –
