Using SQL JOINs Statements

Rules for using “*” operator and aliasing tables.   

You can use the “*” operator to pull all columns that belong to a particular table instead of typing them individually if your whole purpose is to do that.

For example, the above could be modified to this:

METHOD 1:  Non-Aliasing Tables

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

METHOD 2:  Aliasing Tables

SELECT film.*, actor.FirstName, actor.LastName
	FROM dbo.Films film
	INNER JOIN dbo.Actors actor
    	ON film.FilmID = actor.FilmID

Since all the columns are pulled from the Films table, you don’t need to call out every column name.  Notice that I didn’t include the “*” for actor (the second table) because I’m excluding the “FilmID” column from appearing twice.  Therefore, I have no other choice but to call out the specific columns I needed. You might wonder, why can’t you just use an “*” instead of Production.Films.*?   The answer is because you don’t know which columns from which table since you’re dealing with multiple tables. That’s why it’s still important to list the Table name.

The rule for aliasing tables is that once use it, you must refer to it throughout your statement.  You cannot go back and use the Table.Column method or it will fail and you get and error like the following example:

SELECT film.*, actor.FirstName, actor.LastName
	FROM dbo.Films film
	INNER JOIN dbo.Actors actor
    	ON film.FilmID = dbo.Actors.FilmID

Now, what about if you had a third table for how much each film made at the box office?

TABLE: BOXOFFICE

Here is how you join three or more tables. All tables involved must be connected via the “ON” operator:

SELECT <select list>
  FROM <first_table>
  <join_type> <second_table>
          [ON <join_condition>]
  <join_type> <third_table>
          [ON <join_condition>]
  …
  <join_type> <nth_table>
          [ON <join_condition>]

Let’s apply this to our example tables using both methods:

METHOD 1:  Non-Aliasing Tables

SELECT 	dbo.Films.*, 
		dbo.Actors.FirstName,  
        dbo.Actors.LastName, 
        dbo.BoxOffice.Gross
	FROM dbo.Films
	INNER JOIN dbo.Actors
		ON dbo.Films.FilmID = dbo.Actors.FilmID
			INNER JOIN dbo.BoxOffice
				ON dbo.Films.FilmID = dbo.BoxOffice.FilmID

METHOD 2:  Aliasing Tables

SELECT 	film.*, 
		actor.FirstName, 
        actor.LastName, 
        bo.Gross
	FROM dbo.Films film
	INNER JOIN dbo.Actors actor
		ON film.FilmID = actor.FilmID
		INNER JOIN dbo.BoxOffice bo
			ON film.FilmID = bo.FilmID

Result:  Columns appear the exact order you specify in your select list.

TABLE:  JOINs FROM THREE TABLES:  Films, Actors, BoxOffice

Next, you look at this table and you think to yourself:  What if I want the actors’ names to be listed in single column like Star instead of using two columns like FirstName and LastName?  Can I do that?

Sure! You can conjoin two or more columns into a single column.

Here’s how.

SELECT <column1> + <column2> AS <new_column> FROM <table>

The example below will join the first name and last name into an alias column called “Name”. Notice how the CAST() function is used to convert them to a VARCHAR(35) to make sure nothing gets truncated. This is optional only.

SELECT	CAST(LastName + ', ' + FirstName AS varchar(35)) AS Name, 
		AccountNumber
	FROM Person.Person pp
	JOIN Sales.Customer sc
		ON pp.BusinessEntityID = sc.PersonID

Let’s apply it to our example:

METHOD 1:  Non-Aliasing Tables

SELECT 	dbo.Films.*, 
		dbo.Actors.FirstName + ' ' + dbo.Actors.LastName AS 'Star', 
        dbo.BoxOffice.Gross
	FROM dbo.Films
	INNER JOIN dbo.Actors
		ON dbo.Films.FilmID = dbo.Actors.FilmID
		INNER JOIN dbo.BoxOffice
			ON dbo.Films.FilmID = dbo.BoxOffice.FilmID
            WHERE film.FilmID = 1

METHOD 2:  Aliasing Tables

SELECT	film.*, 
		actor.FirstName + ' ' + actor.LastName AS 'Star', 
        bo.Gross
	FROM dbo.Films film
	INNER JOIN dbo.Actors actor
		ON film.FilmID = actor.FilmID
		INNER JOIN dbo.BoxOffice bo
			ON film.FilmID = bo.FilmID
			WHERE bo.Gross >= 50000000

RESULT:

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

Pages: 1 2 3 4 5