Using SQL JOINs Statements

INNER JOIN  (JOIN) – Exclusive

• Returns only the records where there are matches for whatever field(s) you have said are to be used for the JOIN. 
• It is an exclusive JOIN— that is, it excludes all records that don’t have a value in both tables (the first named, or left table, and the second named, or right table).

The syntax for INNER JOIN (or JOIN):

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

First, let’s say our tables have the following schemas:

dbo.Films and dbo.Actors

So in our example, we could do this:

SELECT * FROM dbo.Films
	INNER JOIN dbo.Actors 
    	ON dbo.Films.FilmID = dbo.Actors.FilmID

The result would look something like the following:

Well, this is nothing like what we had above!  Close but we have two duplicate columns: FilmID appears twice.   This is because we use the wild card “*” operator to select “ALL” from both tables.  We have to be selective with our queries. We also do not want the ActorID column.

So, let’s update this.  We only want the unique columns and eliminate any duplicates.  When you want to refer to a column where the column name exists more than once in your JOIN result, you must fully qualify the column name. You can do this in one of two ways:

Provide the name of the table that the desired column is from, followed by a period and the column name (Table.ColumnName)

SELECT	dbo.Films.FilmID, 
		dbo.Films.FilmName, 
        dbo.Films.YearMade, 
        dbo.Actors.FirstName, 
        dbo.Actors.LastName
    FROM dbo.Films
	INNER JOIN dbo.Actors
  		ON dbo.Films.FilmID = dbo.Actors.FilmID

Alias the tables, and provide that alias, followed by a period and the column name (Alias.ColumnName).

SELECT	film.FilmID 'Film ID', 
		film.FilmName, 
        film.YearMade, 
        actor.FirstName, 
        actor.LastName
  	FROM dbo.Films AS film
  	INNERJOIN dbo.Actors actor
        ON film.FilmID = actor.FilmID

• film = “dbo.Films”
• actor = “dbo.Actors”
• the “AS” keyword is optional

Now, we should get the result we wanted:

TABLE:  TEMPORARY JOINED TABLE

YAY!

Pages: 1 2 3 4 5