Join Queries

-- Create a table and insert data into the table.
gaussdb=# CREATE TABLE emp(
    id int,             -- Employee ID
    name varchar,   -- Employee name
    deptno int      -- Employee department ID
);

gaussdb=# CREATE TABLE dept(
    deptno int,     -- Department ID
    depname varchar -- Department name
);

gaussdb=# INSERT INTO emp VALUES (1, 'Joe', 10), (2, 'Scott', 20), (3, 'Ben', 999);   -- The department of Ben has not been confirmed yet. Therefore, the department ID is 999.
gaussdb=# INSERT INTO dept VALUES (10, 'hr'), (20, 'it'), (30, 'sal');                -- There is no employee in the sal department.

• INNER JOIN

  gaussdb=# SELECT t1.id,t1.name,t2.depname FROM emp t1 JOIN dept t2 ON t1.deptno = t2.deptno;
   id | name  | depname 
  ----+-------+---------
    1 | Joe   | hr
    2 | Scott | it
  (2 rows)

• LEFT JOIN

  gaussdb=# SELECT t1.id,t1.name,t2.depname FROM emp t1 LEFT JOIN dept t2 ON t1.deptno = t2.deptno;
   id | name  | depname 
  ----+-------+---------
    1 | Joe   | hr
    2 | Scott | it
    3 | Ben   | 
  (3 rows)

• RIGHT JOIN

  gaussdb=# SELECT t1.id,t1.name,t2.depname FROM emp t1 RIGHT JOIN dept t2 ON t1.deptno = t2.deptno;
   id | name  | depname 
  ----+-------+---------
    1 | Joe   | hr
    2 | Scott | it
      |       | sal
  (3 rows)

• FULL JOIN

  gaussdb=# SELECT t1.id,t1.name,t2.depname FROM emp t1 FULL JOIN dept t2 ON t1.deptno = t2.deptno;
   id | name  | depname 
  ----+-------+---------
    1 | Joe   | hr
    2 | Scott | it
      |       | sal
    3 | Ben   | 
  (4 rows)
  
  -- Delete.
  gaussdb=# DROP TABLE emp,dept;