Types of SQL Joins

In the SQL join with high-level explanation using the example. There are different types of SQL joins and in this tutorial, we shall discuss them in brief with the examples. The example and the join discussed in the previous tutorial is also known as equijoins. The other types of SQL Joins are:

Self Joins

The simple definition of the join is to combine the rows of the different tables and display them.

The self-joins can replace the subqueries, where the same table is reached again to retrieve the data as the outer table. Although the use case of subqueries can be replaced with the application of the self joins. Moreover, many databases process the operations of self joins faster compared to that of the subqueries.


SELECT cust_id, cust_name, cust_contact

FROM Customers

WHERE cust_name = (SELECT cust_name

    FROM Customers

    WHERE cust_contact = ‘Jim Jones’);


cust_id      cust_name         cust_contact

——–     ————–    ————–

1000000003    Fun4All          Jim Jones

1000000004    Fun4All          Denise L. Stephens

Here, in this example, the data retrieval is obtained from the query inside the query known as subqueries. Instead, this example with the same output can be replaced by the self joins.


SELECT c1.cust_id, c1.cust_name, c1.cust_contact

FROM Customers AS c1, Customers AS c2

WHERE c1.cust_name = c2.cust_name

 AND c2.cust_contact = ‘Jim Jones’;


cust_id       cust_name      cust_contact

——-       ———–    ————–

1000000003    Fun4All        Jim Jones

1000000004    Fun4All        Denise L. Stephens

The output is quite similar. But here, using the self joins the table is named with two different names as c1 and c2 with the concept of table aliases and the data is retrieved from the table by reaching the same table.

Natural Joins

Whenever data is retrieved from the different tables using the standard joins, sometimes there is a chance that at least one table might appear more than once. A natural join can simply eliminate this issue of duplication and only retrieve the unique data from different tables. To obtain the unique columns from the tables, you need to use a wildcard (SELECT *), in the syntax of standard joins to act like natural joins with unique data columns. 


SELECT C.*, O.order_num, O.order_date, OI.prod_id, OI.quantity, OI.item_price

FROM Customers AS C, Orders AS O, OrderItems AS OI

WHERE C.cust_id = O.cust_id

 AND OI.order_num = O.order_num

 AND prod_id = ‘RGAN01’;

Inner Joins

An inner join is also called equijoin. This join performs the equality testing between the different tables. Let us look into the example of inner joins for better understanding if it.


SELECT vend_name, prod_name, prod_price

FROM Vendors INNER JOIN Products

 ON Vendors.vend_id = Products.vend_id;


vend_name            prod_name            prod_price

———               ———-        ———-

Doll House Inc.      Fish bean bag toy    3.4900

Doll House Inc.      Bird bean bag toy    3.4900

Doll House Inc.      Rabbit bean bag toy  3.4900

Bears R Us           8 inch teddy bear    5.9900

Bears R Us           12 inch teddy bear   8.9900

Bears R Us           18 inch teddy bear   11.9900

Doll House Inc.      Raggedy Ann          4.9900

Fun and Games        King doll            9.4900

Fun and Games        Queen doll           9.4900

Outer Joins

Generally, joins relate rows in one table with that of rows in another table. But, in case if one wants to include the rows that have no related rows. 


SELECT Customers.cust_id, Orders.order_num


 ON Customers.cust_id = Orders.cust_id;


cust_id       order_num

———-    ———

1000000001    20005

1000000001    20009

1000000002    NULL

1000000003    20006

1000000004    20007

1000000005    20008

Some of the order numbers, which are null also shown in the output when the outer join is used. 

In the syntax of an outer join, the specific keyword of left or right is used to mention the specific table. 

This is all about the end of this tutorial about the different types of SQL joins and the explanation of different joins with different examples and outputs. For any sort of doubts or questions in this tutorial, you can reach out through the comment box.

Related Tutorials

SQL Joins

WHERE Clause in SQL

Spread knowledge

Leave a Comment

Your email address will not be published. Required fields are marked *