EDUCBA

Home Data Science Data Science Tutorials SQL Tutorial SQL Join Two Tables

SQL Join Two Tables

By Roja MetlaRoja Metla

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

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

  • 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:

SQL Join Two Tables example 1.2

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:example 4

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

We hope that this EDUCBA information on “SQL Join Two Tables” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. SQL Set Operators
  2. Custom SQL in Tableau
  3. SQL Right Join
  4. LIKE Query in SQL
  5. Various Examples of SQL Delete Join