Using SQL JOINs Statements

OUTER JOIN

The OUTER JOIN really just includes the LEFT OUTER JOIN and RIGHT OUTER JOIN.

The general syntax is:

SELECT <SELECT list>
FROM <the table you want to be the “LEFT” table>
  <LEFT|RIGHT> [OUTER] JOIN <table you want to be the “RIGHT” table>
           ON <join condition>

The LEFT OUTER JOIN and RIGHT OUTER JOIN will be discussed next.

LEFT [OUTER] JOIN

• Returns all rows from A (left table)
• And only those matching records from B (right table).

The syntax for the LEFT JOIN:

SELECT <select list>
  FROM <first_table>
  LEFT JOIN <second_table>
          ON <join_condition>

Let’s apply it to our example:  Films that grossed more than $40 million dollars.

METHOD 1:  Non-Aliasing Tables

SELECT dbo.Films.*, dbo.BoxOffice.Gross
  FROM dbo.Films
  LEFT JOIN dbo.BoxOffice
          ON dbo.Films.FilmID = dbo.BoxOffice.FilmID
          WHERE dbo.BoxOffice.Gross > 40000000

METHOD 2:  Aliasing Tables

SELECT film.*, bo.Gross
	FROM dbo.Films film
	LEFT JOIN dbo.BoxOffice bo
		ON film.FilmID = bo.FilmID
		WHERE bo.Gross > 40000000

RESULT:

TABLE:  Combining FirstName and LastName columns into a single column “Star”

TABLE:  Films

RIGHT [OUTER] JOIN

• Returns all rows from B (right table)
• And only those matching records from A (left table)

The syntax for the RIGHT JOIN:

SELECT <select list>
  FROM <first_table>
  RIGHT JOIN <second_table>
          ON <join_condition>

Let’s apply it to our example:  Films that grossed more than $40 million dollars.

METHOD 1:  Non-Aliasing Tables

SELECT dbo.Films.*, dbo.BoxOffice.Gross
	FROM dbo.Films
	RIGHT JOIN dbo.BoxOffice
		ON dbo.Films.FilmID = dbo.BoxOffice.FilmID
        WHERE dbo.BoxOffice.Gross > 40000000

METHOD 2:  Aliasing Tables

SELECT film.*, bo.Gross
	FROM dbo.Films film
	RIGHT JOIN dbo.BoxOffice bo
		ON film.FilmID = bo.FilmID
		WHERE bo.Gross > 40000000

RESULT:

TABLE:  Combining FirstName and LastName columns into a single column “Star”

FULL [OUTER] JOIN

• Return all rows where there is a match in one table

The syntax for the FULL JOIN:

SELECT <select list>
  FROM <first_table>
  FULL JOIN <second_table>
          ON <join_condition>

Let’s apply it to our example:  Films that grossed more than $40 million dollars.

METHOD 1:  Non-Aliasing Tables

SELECT dbo.Films.*, dbo.BoxOffice.Gross
	FROM dbo.Films
	FULL JOIN dbo.BoxOffice
		ON dbo.Films.FilmID = dbo.BoxOffice.FilmID
		WHERE dbo.BoxOffice.Gross > 40000000

METHOD 2:  Aliasing Tables

SELECT film.*, bo.Gross
	FROM dbo.Films film
	RIGHT JOIN dbo.BoxOffice bo
		ON film.FilmID = bo.FilmID  
		WHERE bo.BoxOffice.Gross > 40000000

RESULT:

TABLE:  Combining FirstName and LastName columns into a single column “Star”

Pages: 1 2 3 4 5