Best JOINS In SQL With Examples

If you are a programmer having difficulties on How to use Joins in SQL Query, well I’m glad to say that you are on the right track.

I know that doing a complex system with multiple tables requires a wide knowledge on how to use SQL Joins.

In this article about BEST SQL Joins in SQL with Examples you will learn not only what are SQL joins but also How to use Join in SQL.

This article will surely answers your questions on how to display data from multiple tables.

Frequently Asked Questions about SQL JOINS

Different Types of SQL Join

I use Venn diagram below because I know that this is the easiest way to explain the different types of Joins, which shows all possible logical relations between data sets. 

At this point, I will give in-depth discussion of each types of joins with example based on actual scenarios.

INNER JOIN

 Result is the intersection of the two tables.

Syntax of INNER JOINS in SQL Query

SELECT * FROM TABLE1 T1 INNER JOIN T2 ON T1.ID = T2.ID

Results of INNER JOIN

TABLE USED in the demonstration of JOINS

For the demonstration of LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL and CROSS JOIN we use two table which is the INFO and DETAIL table.

INFO (I) TABLE

DETAIL (D) TABLE

LEFT JOIN

This query will retrieve all rows in the LEFT table even if there is no match in the RIGHT Table.  See the Image Below.

Syntax of LEFT OUTER JOINS in SQL Query

SELECT * FROM INFO I LEFT OUTER JOIN DETAIL D ON I.EMPNO = D.EMPNO

Results:

The image above shows the output of LEFT OUTER JOIN when executed, as you can observe in the “001” value in “EMPNO” is highlighted.

It is highlighted because this is only the value from both table INFO and DETAIL that matched together.

Another thing is, all the data in INFO table is displayed in the result. This is because we are using LEFT OUTER JOIN and the INFO(I) table is placed on the LEFT.

Therefore all the data from the LEFT table will be prioritized in the results.

RIGHT JOIN

This query will retrieve all rows in the RIGHT table even if there is no match in the LEFT Table.  See the Image Below.

Syntax of RIGHT OUTER JOINS in SQL Query

SELECT * FROM INFO I RIGHT OUTER JOIN DETAIL D ON I.EMPNO = D.EMPNO

Results

The image above shows the output of RIGHT OUTER JOIN when executed, as you can observe in the “001” value in “EMPNO” is highlighted.

Only the difference of RIGHT JOIN to LEFT JOIN, is that it only prioritized the RIGHT table which is the DETAIL(D) table.

FULL JOIN

This query will retrieve all rows from different tables even if there is no match.

See the image below.

Syntax of FULL OUTER JOINS in SQL Query

SELECT * FROM INFO I FULL OUTER JOIN DETAIL D ON I.EMPNO = D.EMPNO

Output of FULL OUTER JOIN

In above example, it combines all the results both from LEFT [ INFO (I) ] and RIGHT [ DETAIL (D) ] table.

It also returns all (matched or unmatched) rows from the tables on both sides of the join clause.

Additional Conditions

SELECT * FROM INFO I JOIN DETAIL D
ON (I.EMPNO = D.EMPNO)
AND I.FNAME= 'ADAM'

Results:

Code Explanation

In above code, you can apply additional conditions in the WHERE clause. The Example shown performs a join on the INFO(I) and DETAIL(D) tables, and, in addition, displays only employees with a First Name equal to ADAM.

CROSS JOIN

Cartesian product

– When a join condition is invalid or omitted completely, the result is a Cartesian product, in which all combinations of rows are displayed.

– All rows in the first table are joined to all rows in the second table. In order to avoid Cartesian product, you need to include a valid join condition in a WHERE clause.

See the image below for a clear vision of what is a cross join.

Syntax of FULL OUTER JOINS in SQL Query

SELECT * FROM INFO I, DETAIL D

Results of SQL Query

This query retrieves all rows in the INFO(I) table, even if there is no match from the DETAIL(D) table. It retrieves all rows in the DETAIL(D) table, even if there is no match in the INFO(I) table.

Summary

In summary, we have discuss about what are different types of Joins and How to use it with practical examples.

Cartesian Products

A Cartesian product results in all combination of rows displayed. This is done by either omitting the WHERE clause or specifying the CROSS JOIN clause.

Table Aliases

Table aliases speed up database access and it can help to keep SQL code smaller, by conserving memory.

Related Article

• Writing Basic SQL Select Statements With Actual Demonstration
• SQL SELECT Statement WHERE | Restricting and Sorting of Data
• Logical Operators In SQL [ AND, OR, NOT ]
• Best MySQL Tutorial For Beginners in 7 Days
• DBMS Mini Projects Topics for Students 2020

Inquiries

I hope that you have learned something new today. If you have any questions or suggestions about the Different Types of Joins in SQL with examples, please feel free to leave a comment below.