SQL Joins : Beginner’s Ride in SQL Universe

Just co-relate the SQL Join with our Virtual meetups via different source like Zoom, Hangout, Skype etc. we use only one software/tool to collaborate with each other right ?? even though we are from all over the world.

The SQL join is similar — Using one single Join query we get the filtered Result of each and every Table in the Database.

Most importantly — according to what you want to view.

Enjoy the Join Ride of Mars and Earth in the below sequence: Need ,Definition ,Types ,Syntax ,Examples ,Interesting Facts are explained below!

Why do we need SQL Join?

In SQL a JOIN is needed to get details about the reference data. There are times when the data gets repeated in a table and you would not like to store it for every record repeatedly. Not only it will take extra space, but could lead to update/delete anomalies and thus having inconsistent/redundant data.

Definition :

SQL Join is used to fetch data from two or more tables, which is joined to appear as single set of data.
A JOIN clause is used to combine rows from two or more tables, based on a related column between them.

Different Types of SQL JOINs

Here are the different types of the JOINs in SQL:

• (INNER) JOIN: Returns records that have matching values in both tables.

• LEFT (OUTER) JOIN: Return all records from the left table, and the matched records from the right table.

• RIGHT (OUTER) JOIN: Return all records from the right table, and the matched records from the left table.

• FULL (OUTER) JOIN: Return all records when there is a match in either left or right table

Syntax :

Best way to remember SQL Joins using Earth and Mars Example with Syntax:

Best way to remember SQL Joins using Earth and Mars Example: I found above Image really self explanatory

Example:

Lets say we have below Demo Data-set of “Customers” and “Orders”.

Source: here

1. Inner Join:

The following SQL statement selects all orders with customer and shipper information:
Note: The INNER JOIN keyword selects all rows from both tables as long as there is a match between the columns. If there are records in the “Orders” table that do not have matches in “Customers”, these orders will not be shown!

Source: here

2. Right Join :

The following SQL statement will return all employees, and any orders they might have placed

Note: The RIGHT JOIN keyword returns all records from the right table (Employees), even if there are no matches in the left table (Orders).

Source: here

3. Left Join :

The following SQL statement will select all customers, and any orders they might have

Note: The LEFT JOIN keyword returns all records from the left table (Customers), even if there are no matches in the right table (Orders).

Source: here

4. Outer Join :

The following SQL statement selects all customers, and all orders:

Note: The FULL OUTER JOIN keyword returns all the rows from the left table (Customers), and all the rows from the right table (Orders). If there are rows in “Customers” that do not have matches in “Orders”, or if there are rows in “Orders” that do not have matches in “Customers”, those rows will be listed as well.

Source: here

Interesting Fact :

For INNER joins the order doesn’t matter

For (LEFT, RIGHT or FULL) OUTER joins,the order matter

Conclusion :

1. Use a full outer join when you want all the results from both sets.

2. Use an inner join when you want only the results that appear in both sets.

3. Use a left outer join when you want all the results from set a, but if set b has data relevant to some of set a’s records, then you also want to use that data in the same query too.

Want to Learn More About SQL JOINs?

Best Resource: W3schools (Above example is taken from this source as well)