Best JOINS In SQL With Examples

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

What is JOIN in SQL?

A JOIN clause is used to combine rows from two or more tables, based on a related column between them.

What are SQL Joins?

There four main SQL Joins: Inner, left, Right, and Full Joins.

How to use Join in SQL?

To use join in SQL, you need to understand first the concept of Different Types of SQL Joins. This will be discussed the in the following paragraph.

Different Types of SQL Join

Different Types of Joins in SQL with Example

  • Inner Join

    The result of INNER Join in SQL is the intersection of the two tables.inner join in sql

  • Left Outer Join ( or Left Join for Short )

    This Joins in SQL Query will retrieve all rows in the LEFT table even if there is no match in the RIGHT Table.
    Left Join in SQL

  • Right Outer Join ( or Right Join for Short )

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

  • Full Outer Join (or Full Join for Short )

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

  • Cross Join

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

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
INNER JOIN in SQL TABLE
INNER JOIN in SQL TABLE

Results of INNER JOIN

RESULTS OF INNER JOIN SQL
RESULTS OF INNER JOIN SQL

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

EMPNOFNAMELNAME
001ADAMWAYNES
002LEAHHOPKINS
003XYREDAVIS
INFO TABLE

DETAIL (D) TABLE

EMPNOROLEEXTTITLE
001MGR445MRS.
004EXEC101DR.
DETAIL 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:

LEFT OUTER JOIN sample output
LEFT OUTER JOIN sample output

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

right outer join in sql results
right join 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

FULL OUTER JOIN in SQL results
FULL OUTER JOIN in SQL results

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.

Cross Join in sql
This image owned by www.w3resource.com

Syntax of FULL OUTER JOINS in SQL Query

SELECT * FROM INFO I, DETAIL D

Results of SQL Query

cross join in sql results
cross join in sql results

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

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.

1 thought on “Best JOINS In SQL With Examples”

Leave a Comment