Database 04

👋🏾, welcome back.

Definition

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

Different Types of SQL Joins

1. INNER JOIN: returns records that have matching values in both tables
2. LEFT(OUTER) JOIN: return all records from the left and the matched ones from the right table, the result is zero records from the right side, if there is no match.
3. RIGHT(OUTER) JOIN: return all records from the right and the matched ones from the left table.
4. FULL (OUTER) JOIN: returns all records in both left and right tables.

Types

SQL INNER JOIN Keyword

SELECT column_name(s) 
FROM table1
INNER JOIN table2 
ON table1.column_name = table2.column_name;

SQL LEFT (OUTER) JOIN Keyword

SELECT column_name(s) 
FROM table1
LEFT JOIN table2 
ON table1.column_name = table2.column_name;

SQL RIGHT (OUTER) JOIN Keyword

SELECT column_name(s) 
FROM table1
RIGHT JOIN table2 
ON table1.column_name = table2.column_name;

SQL FULL OUTER JOIN Keyword

• This keyword returns all records when there are a match in left (table1) or right (table2) table records.
• Full outer join can potentially return very large result-sets.
• The FULL OUTER JOIN keyword returns all matching records from both tables whether the other table matches or not.

SELECT column_name(s) 
FROM table1
FULL OUTER table2
ON table1.column_name = table2.column_name
WHERE condition;

SQL Self Join

• It is a regular join, but the table is joined with itself.

SQL UNION Operator

• This operator is used to combine the result-set of two or more SELECT statements.
  • every SELECT statement in UNION must have the same number of columns.
  • columns must have the same data types.
  • columns in every SELECT statement must also be in the same order.