DAY5 OF PSQL Using Join Query

Types of JOINs in PostgreSQL

1. INNER JOIN

   Returns only the rows that have matching values in both tables.

   Syntax:

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

Example:

SELECT employees.id, employees.name, departments.name AS department
FROM employees
INNER JOIN departments ON employees.department_id = departments.id;

1. LEFT JOIN (LEFT OUTER JOIN)

   Returns all rows from the left table and matching rows from the right table. If no match, it returns NULL for the right table columns.

   Syntax:

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

Example:

SELECT employees.id, employees.name, departments.name AS department
FROM employees
LEFT JOIN departments ON employees.department_id = departments.id;

1. RIGHT JOIN (RIGHT OUTER JOIN)

   Returns all rows from the right table and matching rows from the left table. If no match, it returns NULL for the left table columns.

   Syntax:

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

Example:

SELECT employees.id, employees.name, departments.name AS department
FROM employees
RIGHT JOIN departments ON employees.department_id = departments.id;

1. FULL JOIN (FULL OUTER JOIN)

   Returns all rows when there is a match in either table. If no match, NULL values are returned.

   Syntax:

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

Example:

SELECT employees.id, employees.name, departments.name AS department
FROM employees
FULL JOIN departments ON employees.department_id = departments.id;

1. CROSS JOIN

   Returns the Cartesian product of both tables (each row of table1 is combined with every row of table2).

   Syntax:

SELECT table1.column1, table2.column2
FROM table1
CROSS JOIN table2;

Example:

SELECT employees.name, departments.name AS department
FROM employees
CROSS JOIN departments;

1. SELF JOIN

   A table joins itself to compare rows.

   Example:

   SELECT e1.name AS employee, e2.name AS manager
   FROM employees e1
   JOIN employees e2 ON e1.manager_id = e2.id;

2. JOIN using USING (Simplified JOIN)

   If both tables have the same column name, you can use USING instead of ON.

   Example:

SELECT employees.id, employees.name, departments.name AS department
FROM employees
JOIN departments USING (department_id);