Using SQL JOINs Statements

CROSS JOIN

• Returns a Cartesian product of two or more tables
• Does not have an ON operator
• Does not have a WHERE clause (However, if used it behaves like an INNER JOIN)

The syntax for the CROSS JOIN:

SELECT <select list>
  FROM <first_table>
  CROSS JOIN <second_table>

Let’s apply it to our example.

METHOD 1:  Non-Aliasing Tables

SELECT dbo.Films.*, dbo.BoxOffice.Gross
	FROM dbo.Films
	CROSS JOIN dbo.BoxOffice

METHOD 2:  Aliasing Tables

SELECT film.*, bo.Gross
	FROM dbo.Films film
	CROSS JOIN dbo.BoxOffice bo        

RESULT:  A Cartesian Table with 16 Rows (4 x 4) similar to below.

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

If you switch tables in CROSS JOIN:

SELECT film.*, bo.Gross
	FROM dbo.BoxOffice bo
	CROSS JOIN dbo.Films film

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

If a WHERE clause is added, the CROSS JOIN behaves as an INNER JOIN. For example, the following Transact-SQL queries produce the same result set.

USE Movies
GO
SELECT * FROM dbo.Films
	CROSS JOIN dbo.Actors
	WHERE dbo.Films.FilmID = dbo.Actors.FilmID

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

UNION

• UNION is a special operator you can use to cause two or more queries to generate one result set.

When dealing with queries that use a UNION, there are just a few key points:

• All the UNIONed queries must have the same number of columns in the SELECT list. If your first query has three columns in the SELECT list, the second (and any subsequent queries being UNIONed) must also have three columns. If the first has five, the second must have five, too. Regardless of how many columns are in the first query, there must be the same number in the subsequent query(s).
• The headings returned for the combined result set will be taken only from the first of the queries. If your first query has a SELECT list that looks like SELECT Col1, Col2 AS Second, Col3 FROM..., regardless of how your columns are named or aliased in the subsequent queries, the headings on the columns returned from the UNION will be Col1, Second, and Col3 respectively.
• The data types of each column in a query must be implicitly compatible with the data type in the same relative column in the other queries. Note that I’m not saying they have to be the same data type — they just have to be implicitly convertible. If the second column in the first query were of type char(20), it would be fine if the second column in the second query were varchar(50). However, because things are based on the first query, any rows longer than 20 would be truncated for data from the second result set.
• Unlike non-UNION queries, the default return option for UNION is DISTINCT rather than ALL. This can really be confusing to people. In your other queries, all rows were returned regardless of whether they were duplicated with another row, but the results of a UNION do not work that way. Unless you use the ALL keyword in your query, only one of any repeating rows will be returned.

SELECT	dbo.Films.FilmID, 
		dbo.Films.FilmName, 
        dbo.Films.YearMade   
	FROM dbo.Films
UNION
SELECT	dbo.BoxOffice.FilmID, 
		dbo.BoxOffice.FilmName, 
        dbo.BoxOffice.Gross
	FROM dbo.BoxOffice

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

Pages: 1 2 3 4 5