SQL queries for various join types

Table of Contents

1. Single Table Extraction

SELECT * FROM table_name;

SELECT table1.column1, table2.column2
FROM table1
INNER JOIN table2 ON table1.column1 = table2.column2;

This query joins two tables based on an equality condition between columns in both tables.
It selects specific columns from each table and returns only rows where the specified columns have matching values.

SELECT table1.column1, table2.column2
FROM table1
LEFT JOIN table2 ON table1.column1 > table2.column2;

This query joins two tables based on a non-equality condition between columns.
It uses a LEFT JOIN to ensure all rows from the left table are included, even if no matching rows exist in the right table.

SELECT e1.name AS employee_name, e2.name AS manager_name
FROM employees e1
INNER JOIN employees e2 ON e1.manager_id = e2.employee_id;

This query joins a table with itself.
It uses aliases to distinguish columns from the same table and selects specific columns to retrieve specific information (e.g., employee names and their managers).

SELECT table1.column1, table2.column2
FROM table1
LEFT OUTER JOIN table2 ON table1.column1 = table2.column2;

This query includes all rows from the left table and matching rows from the right table.
It replaces unmatched values from the right table with NULL.

SELECT table1.column1, table2.column2
FROM table1
RIGHT OUTER JOIN table2 ON table1.column1 = table2.column2;

This query includes all rows from the right table and matching rows from the left table.
It replaces unmatched values from the left table with NULL.

SELECT table1.column1, table2.column2
FROM table1
FULL OUTER JOIN table2 ON table1.column1 = table2.column2;

This query includes all rows from both tables, regardless of whether they have matching values.
It replaces unmatched values with NULL in both tables.