DatabasesSQL Server

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:

1My Fair Lady196411RexHarrison
1My Fair Lady196421AudreyHepburn
2The Terminator198432ArnoldSchwarzenegger
3Superman III198343ChristopherReeve

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:

Image result for 1 png

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, 
    FROM dbo.Films
	INNER JOIN dbo.Actors
  		ON dbo.Films.FilmID = dbo.Actors.FilmID
Image result for 1 png

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

SELECT	film.FilmID 'Film ID', 
  	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:


1My Fair Lady1964RexHarrison
1My Fair Lady1964AudreyHepburn
2The Terminator1984ArnoldSchwarzenegger
3Superman III1983ChristopherReeve


Verified by MonsterInsights