JOINs in SQL | How to JOIN two tables in SQL

What are JOINs in SQL?

JOINs in SQL are feature by which we combine rows of two SQL tables through SQL columns of both tables. The column that we used to combine both SQL tables must have common records. To understand this we will take an example as below.

Let’s suppose we have table EMPLOYEES wherein we have details of all employees in the organization. The details of EMPLOYEES SQL table are illustrated in the below image.

We also have another table JOBS in which we have details of minimum and maximum salary by designation.

Now if we want to get salary slabs for each employee we need to combine both the oracle tables. to combine both the tables we need to find a common column that is available in both SQL tables. In this example, we can notice JOB_ID is common in both the tables. So we will write a SQL query with SQL join clause to combine the records.

SELECT EM.*,JB.MIN_SALARY, JB.MAX_SALARY FROM
(SELECT * FROM EMPLOYEES) EM
JOIN
(SELECT * FROM JOBS) JB
ON EM.JOB_ID=JB.JOB_ID;

In the above SQL code, we use JOIN CLAUSE to join the table with using ON to combine the columns JOB_ID. Here it is also important to notice that we EM and JB as SQL alias and we use EM.* to get all columns from EM(EMPLOYEES) table and specify two columns of JB(JOBS) table to control the number of columns from the second SQL column. The final result of JOIN will be like below.

In the below section we will be discussing types of JOINs in SQL with examples.

INNER JOIN in SQL

SQL INNER JOIN is the most commonly used join SQL. It is also referred to as JOIN. The function of INNER JOIN is to combine all rows which are matching in both the tables. It will fetch only common rows from both tables. Let’s see how to use INNER JOIN.

SELECT columns FROM table1
INNER JOIN table2 
ON table1.column = table2.column;

LEFT JOIN in SQL OR LEFT OUTER JOIN in SQL

LEFT JOIN SQL is the second most used SQL JOINs. The main function of LEFT OUTER JOIN is to combine values from two tables but keep LEFT table as base and match records from second table. If there is no match in the second table records against first table will be null. Let’s understand left outer join example.

Below mentioned is LEFT JOIN syntax. We can use LEFT JOIN or LEFT OUTER JOIN as well in the query and the result will be the same from both SQL query.

SELECT columns FROM table1 
LEFT [OUTER] JOIN table2 
ON table1.column = table2.column;

RIGHT JOIN in SQL or RIGHT OUTER JOIN in SQL

SQL RIGHT JOIN is also known as RIGHT OUTER JOIN in SQL. What is the function of a right outer join is to get match data from two tables but the base table will be right side table. All values which will not match will remain null value in left side table.

Syntax of RIGHT OUTER JOIN in SQL is as mentioned below

SELECT columns FROM table1
RIGHT [OUTER] JOIN table2 
ON table1.column = table2.column;

FULL OUTER JOIN in SQL

SQL FULL JOIN which is also known as SQL FULL OUTER JOIN is a part of Oracle JOINs. The main function of FULL OUTER JOIN is to returns all rows from the LEFT side table and RIGHT side table with nulls in place where the join condition does not meet.

Syntax for full outer join in SQL example as given below

SELECT columns FROM table1
FULL [OUTER] JOIN table2 
ON table1.column = table2.column;

You can learn more about join in SQL example from the below video. It also explains multiple table join in SQL. JOIN in SQL with Example.

You can read more about Other SQL topics from this link or here.