❮ Linux Install Postgresql Postgresql Index ❯

PostgreSQL Joins

The PostgreSQL JOIN clause is used to combine rows from two or more tables based on a related column between them.

In PostgreSQL, there are five types of joins:

CROSS JOIN: Cross join
INNER JOIN: Inner join
LEFT OUTER JOIN: Left outer join
RIGHT OUTER JOIN: Right outer join
FULL OUTER JOIN: Full outer join

Next, let's create two tables, COMPANY and DEPARTMENT.

Example

Create the COMPANY table (download COMPANY SQL file), with the following data content:

tutorialprodb# select * from COMPANY;
 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

We add a few records to the table:

INSERT INTO COMPANY VALUES (8, 'Paul', 24, 'Houston', 20000.00);
INSERT INTO COMPANY VALUES (9, 'James', 44, 'Norway', 5000.00);
INSERT INTO COMPANY VALUES (10, 'James', 45, 'Texas', 5000.00);

At this point, the COMPANY table records are as follows:

id | name  | age | address      | salary
 ----+-------+-----+--------------+--------
   1 | Paul  |  32 | California   |  20000
   2 | Allen |  25 | Texas        |  15000
   3 | Teddy |  23 | Norway       |  20000
   4 | Mark  |  25 | Rich-Mond    |  65000
   5 | David |  27 | Texas        |  85000
   6 | Kim   |  22 | South-Hall   |  45000
   7 | James |  24 | Houston      |  10000
   8 | Paul  |  24 | Houston      |  20000
   9 | James |  44 | Norway       |   5000
  10 | James |  45 | Texas        |   5000
(10 rows)

Create a DEPARTMENT table with three fields:

CREATE TABLE DEPARTMENT(
   ID INT PRIMARY KEY      NOT NULL,
   DEPT           CHAR(50) NOT NULL,
   EMP_ID         INT      NOT NULL
);

Insert three records into the DEPARTMENT table:

INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID) VALUES (1, 'IT Billing', 1 );

INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID) VALUES (2, 'Engineering', 2 );

INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID) VALUES (3, 'Finance', 7 );

At this point, the DEPARTMENT table records are as follows:

id | dept        | emp_id
----+-------------+--------
  1 | IT Billing  |  1
  2 | Engineering |  2
  3 | Finance     |  7
(3 rows)

2 | Engineering | 2 3 | Finance | 7

Cross Join

A cross join matches every row of the first table with every row of the second table. If the input tables have x and y rows respectively, the result table will have x*y rows.

Due to the potential for very large tables with a cross join, it must be used with caution and only when appropriate.

Here is the basic syntax for a cross join:

SELECT ... FROM table1 CROSS JOIN table2 ...

Based on the table above, we can write a cross join as follows:

tutorialprodb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY CROSS JOIN DEPARTMENT;

The result is as follows:

tutorialprodb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY CROSS JOIN DEPARTMENT;
 emp_id | name  |       dept
--------+-------+--------------------
      1 | Paul  | IT Billing
      1 | Allen | IT Billing
      1 | Teddy | IT Billing
      1 | Mark  | IT Billing
      1 | David | IT Billing
      1 | Kim   | IT Billing
      1 | James | IT Billing
      1 | Paul  | IT Billing
      1 | James | IT Billing
      1 | James | IT Billing
      2 | Paul  | Engineering
      2 | Allen | Engineering
      2 | Teddy | Engineering
      2 | Mark  | Engineering
      2 | David | Engineering
      2 | Kim   | Engineering
      2 | James | Engineering
      2 | Paul  | Engineering
      2 | James | Engineering
      2 | James | Engineering
      7 | Paul  | Finance

Inner Join

An inner join creates a new result table by combining column values of two tables (table1 and table2) based on the join-predicate. The query compares each row of table1 with each row of table2 to find all pairs of rows which satisfy the join-predicate.

When the join-predicate is satisfied, column values for each matched pair of rows of A and B are combined into a result row.

An inner join is the most common type of join and is the default.

The INNER keyword is optional.

Here is the syntax for an inner join:

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

Based on the table above, we can write an inner join as follows:

tutorialprodb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID;
 emp_id | name  |        dept
--------+-------+--------------
      1 | Paul  | IT Billing
      2 | Allen | Engineering
      7 | James | Finance
(3 rows)

Left Outer Join

Outer joins are extensions of inner joins. SQL standard defines three types of outer joins: LEFT, RIGHT, and FULL, and PostgreSQL supports all of these.

For a left outer join, first, an inner join is performed. Then, for each row in table T1 that does not satisfy the join condition with any row in table T2, a joined row is added with null values in columns of T2. Thus, the joined table unconditionally has at least one row for each row in T1.

Here is the basic syntax for a left outer join:

Based on the two tables, we can write a left outer join as follows:


---

## Right Outer Join

First, perform an inner join. Then, for each row in table T2 that does not satisfy the join condition with any row in table T1, a joined row is added with null values in the columns of T1. This is the opposite of a left join; for each row in T2, there is always a row in the result table.

Here is the basic syntax for a right outer join (RIGHT OUTER JOIN):

SELECT ... FROM table1 RIGHT OUTER JOIN table2 ON conditional_expression ...


Based on the two tables, we can create a right outer join:


---

## Full Outer Join

First, perform an inner join. Then, for each row in table T1 that does not satisfy the join condition with any row in table T2, a row is added to the result with null values in the columns of T2. Additionally, for each row in table T2 that does not satisfy the join condition with any row in table T1, a row is added to the result with null values in the columns of T1.

Here is the basic syntax for a full outer join:

SELECT ... FROM table1 FULL OUTER JOIN table2 ON conditional_expression ...


Based on the two tables, we can create a full outer join:

❮ Linux Install Postgresql Postgresql Index ❯