Joined Relations (SQL)

Using the JOIN keyword, we can specify “joined relations”. Two joined relations look like any other relation.

There are many different types of joins:

• Join Condition (SQL)

  We can retrieve the name and address of all employees who work for the ‘Research’ department by specifying a join condition:

  SELECT FNAME, LNAME, ADDRESS
  FROM   EMPLOYEE, DEPARTMENT
  WHERE  DNAME = 'Research' AND DNUMBER = DNO

  We can specify as many join conditions as we want.

  We can also use an explicit JOIN clause:

  SELECT location, mgrssn
  FROM   DEPARTMENT
  JOIN   DEPT_LOCATIONS
  ON     DEPARTMENT.number=DEPT_LOCATIONS.number
  WHERE  DNAME = 'Research';

  Link to original

• Natural Join (SQL)

  Natural Join (NATURAL JOIN): (same as JOIN) no join condition may be specified, implicit condition to join on attributes with the same name

  Link to original

• Inner Join (SQL)

  Inner Join (INNER JOIN): tuple is included in the result only if a matching tuple exists in the other relation (this is the default type of JOIN)

  Link to original

• Outer Join (SQL)

  Outer Join: all matching tuples are returned are returned (depending on type)

  • Left Outer Join (SQL)

    Left Outer Join (LEFT OUTER JOIN): Keeps values from the left table even if they are not matched.

    SELECT E.name, S.fname
    FROM (
    					EMPLOYEE E
    	LEFT OUTER JOIN EMPLOYEE AS S
    				 ON E.super_ssn=S.ssn
    )

    This will match employees without supervisors.

    Link to original

  • Right Outer Join (SQL)

    Right Outer Join (RIGHT OUTER JOIN): Keeps values from the right table even if they are not matched.

    SELECT E.name, S.fname
    FROM (
    		             EMPLOYEE E
    	RIGHT OUTER JOIN EMPLOYEE AS S
    				  ON E.super_ssn=S.ssn
    )

    This will match employees who are not supervising anyone.

    Link to original

  • Full Outer Join (SQL)

    Full Outer Join (FULL OUTER JOIN): Keeps values from the both tables even if they are not matched.

    SELECT E.name, S.fname
    FROM (
    					EMPLOYEE E
    	FULL OUTER JOIN EMPLOYEE AS S
    				 ON E.super_ssn=S.ssn
    )

    This will match employees without supervisors and employees not supervising anyone.

    Link to original

  Link to original

• Cross Join (SQL)

  Cross Join (CROSS JOIN): Returns the Cartesian Product of two or more tables.

  SELECT E.name, S.name
  FROM (
  	           EMPLOYEE E
  	CROSS JOIN EMPLOYEE S
  )

  Link to original

• Multi-way joins: we can chain multiple joins in one query for multiple tables.