• Inner join
• Left outer join
• Right outer join
• Exception join
• Cross join

Inner join:

An inner join exclusively presents the rows from each table where there are corresponding values in the join columns. Rows lacking a match between the tables are omitted from the resulting table.

There are two ways to perform an inner join:

• JOIN syntax
• WHERE clause

• Using Join Syntax:

  SELECT STUNO, LASTNAME, PROJNO, MARKS
  FROM GRAD.STUDENT INNER JOIN GRAD.PROJECT
  ON STUNO = STUID
  WHERE MARKS > 60
  

  

  Consider the below example, where we need to fetch a student’s project details alongwith the student’s lastname, roll number, project number and marks. The student’s identity details are in table STUDENT and the project details are stored in PROJECT table. So, to identify what project is assigned to a student we would require a relationship between STUDENT and PROJECT table, this relationship can be called a common column on which the join will be performed. In our case those columns are STUNO and STUID.

• Using Where clause:To achieve the equivalent join as the JOIN syntax using the WHERE clause, include both the join condition and any additional selection condition within the WHERE clause. The tables intended for joining are specified in the FROM clause, separated by commas. See the example below.

  SELECT STUNO, LASTNAME, PROJNO, MARKS
  FROM GRAD.STUDENT , GRAD.PROJECT
  WHERE STUNO = STUID
  and MARKS > 60
  

Left outer join:

A left outer join retrieves all the rows obtained from an inner join, along with an additional row for each unmatched row in the first table.

Consider a scenario where you aim to identify all students and their current project assignments, including those who aren’t currently overseeing any projects. The subsequent query will furnish the details of all students with marks greater than 60, along with the project numbers they’re assigned to.

SELECT STUNO, LASTNAME, PROJNO, MARKS
FROM GRAD.STUDENT LEFT OUTER JOIN GRAD.PROJECT
ON STUNO = STUID
WHERE MARKS > 60

Right outer join:

A right outer join retrieves all the rows obtained from an inner join, along with an additional row for each unmatched row in the second table.

Consider a scenario where you aim to identify all projects and the assigned students, including projects which aren’t currently assigned to any student. The subsequent query will furnish the details of all the projects where project group is ‘SCIENCE’, along with the student details.

SELECT PROJNO, STUNO, LASTNAME, MARKS
FROM GRAD.STUDENT RIGHT OUTER JOIN GRAD.PROJECT
ON STUNO = STUID
WHERE PROJGRP =’SCIENCE’

Exception join:

An exception selectively retrieves only the rows from the first table that lack a corresponding match in the second table based on the join condition.

Utilizing the identical tables as previously mentioned, we will fetch the student details who aren’t assigned to any projects.

SELECT STUNO, LASTNAME, PROJNO, MARKS
FROM GRAD.STUDENT EXCEPTION JOIN GRAD.PROJECT
ON STUNO = STUID
WHERE MARKS > 60

Cross join:

A cross join, also referred to as a Cartesian Product join, produces a resultant table wherein each row from the first table is paired with every row from the second table. The quantity of rows in the resultant table equals the product of the row count in each table. The result of a cross join will include all the combinations of records from the two tables.

When the involved tables are extensive, this join operation may consume significant time. So, it is advised to filter the tables with a selection criterion that reduces the number of resulting rows as per your requirement.

Consider the tables in our previous examples viz. STUDENT and PROJECT, when a cross join will be performed each row in table STUDENT will be joined with every row in table PROJECT.

SELECT * FROM STUDENT CROSS JOIN PROJECT