SQL Joins for Efficient Database Queries

SQL is the most demanding skill from Dacades and will be the most demanding in the future. Whatever system we build deals with data for sure, though NoSQL is getting popular day by day SQL has its importance and use.

Sample Tables

Inner Join in SQL

• Inner join produces only the set of records that match in both Table A and Table B.
• Inner Joins do not have to use equality to join the fields.
• Canuse<, >,<>
• Most commonly used

SELECT * FROM TableA
INNER JOIN TableB ON
TableA.PK = TableB.PK
//This is the same as doing
SELECT * FROM TableA, TableB WHERE TableA.PK = TableB.PK

SELECT *
FROMTableA
INNER JOIN TableB ON TableA.PK > TableB.PK

Natural Join in SQL

A Natural Join is just an inner equi-join where the join is implicitly created using any matching columns between the two tables

Example

SELECT * FROM TableA NATURAL JOIN TableB

Left Outer Join in SQL

The left outer join produces a complete set of records from Table A, with the matching records (where available) in Table B. If there is no match, the right side will contain null.

SELECT * FROM TableA LEFT OUTER JOIN TableB ON TableA.PK = TableB.PK

Right Outer Join in SQL

The right outer join produces a complete set of records from Table B, with the matching records (where available) in Table A. If there is no match, the left side will contain null.

SELECT * FROM TableA RIGHT OUTER JOIN TableB ON TableA.PK = TableB.PK

Full Outer Join in SQL

Full outer join produces the set of all records in Table A and Table B, with matching records from both sides where available. If there is no match, the missing side will contain null.

SELECT * FROM TableA FULL OUTER JOIN TableB ON TableA.PK = TableB.PK

MySQL doesn’t have FULL OUTER JOIN

Simulate using UNION, LEFT and RIGHT JOINs

SELECT * FROM TableA LEFT JOIN TableB ON TableA.PK = TableB.PK
UNION
SELECT * FROM TableA RIGHT JOIN TableB
ON TableA.PK = TableB.PK

Left Join Excluding Inner Join

This query will return all of the records in the left table (table A) that do not match any records in the right table (table B).

SELECT * FROM TableA LEFT JOIN TableB ON TableA.PK = TableB.PK WHERE TableB.PK IS NULL

Perform left outer join, then exclude the records we don't want from the right side via a where clause.

Right Join Excluding Inner Join

This query will return all of the records in the right table (table B) that do not match any records in the left table (table A).

SELECT * FROM TableA RIGHT JOIN TableB ON TableA.PK = TableB.PK WHERE TableA.PK IS NULL

Perform right outer join, then exclude the records we don't want from the left side via a where clause.

Full Outer Excluding Inner Join

This query will return all of the records in Table A and Table B that do not have a matching record in the other table.

SELECT * FROM TableA FULL OUTER JOIN TableB ON TableA.PK = TableB.PK
WHERE TableA.PK IS NULL OR TableB.PK IS NULL

Cross Join

• A cross join is a Cartesian Product join – it is every record in Table A combined with every record in Table B.
• It gives the same results as not using a WHERE clause when querying two tables in MySQL
• SELECT * from TableA CROSS JOIN TableB
• SELECT * from TableA, TableB