PostgreSQL JOIN Keyword

The PostgreSQL JOIN keyword is used to combine rows of two or more tables based on common column between them. There are five types of JOINs in PostgreSQL:

• INNER JOIN: It is sometimes called simple JOIN. It returns records based on matching rows in both tables.
• LEFT JOIN: It is sometimes called LEFT OUTER JOIN. It returns records which contains all rows from left table and matching rows from right tables.
• RIGHT JOIN: It is sometimes called RIGHT OUTER JOIN. It returns records which contains all rows from right table and matching rows from left tables.
• FULL JOIN: It is sometimes called FULL OUTER JOIN. It returns records which contains all rows from both tables.
• CROSS JOIN: Returns records which contains all rows from both tables. It is sometimes called CARTESIAN JOIN because in the absence of a WHERE condition it behaves like a CARTESIAN PRODUCT i.e., the number of rows in the result-set is the product of the number of rows of the two tables.

Example:

Consider a database containing tables called Employee and Contact_Info with the following records:

Table 1: Employee table

Table 2: Contact_Info table

• The JOIN keyword is used with Employee and Contact_Info tables based on common column EmpID. It returns Name, Age and Address columns based on match in both tables. The query is given below:

  SELECT Employee.Name, Employee.Age, Contact_Info.Address 
  FROM Employee
  JOIN Contact_Info
  ON Employee.EmpID = Contact_Info.EmpID;
  

  
  

  This will produce the result as shown below:

  Name	Age	Address
  Marry	24	Brooklyn, New York, USA
  Jo	27	Grenelle, Paris, France
  Kim	30	Geuzenveld, Amsterdam, Netherlands
  Huang	28	Yizhuangzhen, Beijing, China