SQL Joins: Inner, Left, Right, Outer

Types of SQL Joins

• SQL joins are categorized into four primary types: inner, left, right, and full outer.
• Joins serve to combine rows from multiple tables based on a shared column.
• Venn diagrams provide a useful way to visually represent the mechanics of different joins.

Inner Join

• Inner joins yield only the rows with matching values across all joined tables.
• This join type returns the intersection of the tables involved.
• Rows lacking a corresponding match in the other table are excluded from the final result.
• For example, joining a customer table and a city table on a city ID will return only the customers who have a matching city in the city table.
• SELECT * FROM customer INNER JOIN city ON customer.cityID = city.cityID; is the SQL syntax.

Left Join

• Left joins return all rows from the left table and only matching rows from the right table
• If a match is absent in the right table, NULL values populate the columns from the right table
• It returns all the matches, plus everything from the left table, even if there is no match in the right table.
• For instance, a left join would include Mary Smith in the results, but the city information would be NULL if no match exists.
• SELECT * FROM customer LEFT JOIN city ON customer.cityID = city.cityID; is the SQL syntax.
• In the SQL query, the "left" table refers to the table positioned on the left side of the JOIN clause.

Right Join

• Right joins return all rows from the right table and only matching rows from the left table.
• NULL values populate the columns from the left table when no match is found.
• Includes all matches, plus everything from the right table, even if there is no match in the left table
• For example, using a right join, Houston would be included in the results, but the customer information would be NULL if no match exists.
• SELECT * FROM customer RIGHT JOIN city ON customer.cityID = city.cityID; is the SQL syntax.
• Switching the order of tables in the FROM clause allows rewriting a right join as a left join.

Full Outer Join

• Full outer joins return all rows from both tables, regardless of matches.
• Returns matches, as well as all records from both tables where there isn't a match
• With no match between tables, NULL values fill the columns from the non-matching table.
• It is essentially a combination of left and right joins.
• For example, Mary Smith and Houston would be included, with NULL values for the columns in the non-matching table
• SELECT * FROM customer FULL OUTER JOIN city ON customer.cityID = city.cityID; is the SQL syntax.

Additional notes on Joins

• The word "OUTER" is optional in LEFT OUTER JOIN, RIGHT OUTER JOIN, and FULL OUTER JOIN.
• Tables can be aliased using the AS keyword to shorten table names within the query.
  • SELECT * FROM customer AS cs INNER JOIN city AS cy ON cs.cityID = cy.cityID; illustrates table aliasing.
• Filtering with the WHERE clause can affect the results differently depending on whether it is used with an inner join or an outer join.
• When using a WHERE clause with an OUTER JOIN, placing the filter on the ON clause filters the table before the join happens, potentially leading to different results
• The order of tables in LEFT and RIGHT JOIN clauses can be switched to achieve the same result.