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.
Example
SELECT cust_id, cust_name, cust_contact
FROM Customers
WHERE cust_name = (SELECT cust_name
FROM Customers
WHERE cust_contact = ‘Jim Jones’);
Output
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.
Example
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’;
Output
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.
Example
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.
Example
SELECT vend_name, prod_name, prod_price
FROM Vendors INNER JOIN Products
ON Vendors.vend_id = Products.vend_id;
Output
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.
Example
SELECT Customers.cust_id, Orders.order_num
FROM Customers LEFT OUTER JOIN Orders
ON Customers.cust_id = Orders.cust_id;
Output
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