PostgreSQL JOINS
To create a new table with the records as a combination of records from multiple tables, the PostgreSQL Join Query is used.
Types of Joins:
The PostgreSQL database supports mainly five types of Joins. These are:
- Inner or Simple Join
- Left Outer Join
- Right Outer Join
- Full Outer Join
- Cross Join
INNER JOIN
The INNER Join being the most popular and common join is often called a SIMPLE Join as it returns all the rows from multiple tables where the join condition is met. Syntax:
SELECT expr_1, expr_2, ... expr_n FROM table_1 INNER JOIN table_2 ON join_predicate;
Parameters: join_predicate: It is used to specify the joining conditions to be strictly followed for joining.
Example: Employment Table:
| ID | STATE | RATE |
| 1 | A | 60 |
| 2 | B | 70 |
| 3 | C | 65 |
| 4 | D | 80 |
| 5 | E | 78 |
Department Table:
| ID | NAME | PERCENT |
| 1 | IT | 60 |
| 2 | SALES | 75 |
| 3 | BANK | 50 |
Query:
SELECT “EMPLOYMENT”.“ID”, “EMPLOYMENT”.“STATE”, “DEPARTMENT”.“NAME” FROM “EMPLOYMENT” INNER JOIN “DEPARTMENT” ON “EMPLOYMENT”.“ID” = “DEPARTMENT”.“ID”; |
Output:
| ID | STATE | NAME |
| 1 | A | IT |
| 2 | B | Sales |
| 3 | C | Bank |
Explanation: The EMPLOYMENT and the DEPARTMENT are the already existing tables that are joined with INNER JOIN query so that the selected fields of the rows satisfying the joining conditions will be displayed as the result.
