 |
|
SQL Server Tips by Burleson |
Joins
Joins are part of ANSI SQL-92 syntax, before this improvement the
only way to relate tables to extract data was using a Cartesian join
and then filtering the result. Cartesian products in ANSI SQL-92
syntax use CROSS JOIN instead of the comma but that is the only
difference.
Note: a Cartesian join is when every row from one table is joined to
every row from another table. The resulting number of rows is the
product of the number of rows from both tables.
Example: Using the ANSI SQL-92 syntax to get all the titles
published by “New Moon Books”:
SELECT dbo.publishers.pub_name,
dbo.titles.title
FROM dbo.publishers INNER JOIN
dbo.titles ON dbo.publishers.pub_id = dbo.titles.pub_id
WHERE (dbo.publishers.pub_name = 'New Moon Books')
The same example using ANSI SQL-86:
SELECT dbo.publishers.pub_name,
dbo.titles.title
FROM dbo.publishers , dbo.titles
WHERE (dbo.publishers.pub_name = 'New Moon Books')
and dbo.publishers.pub_id = dbo.titles.pub_id
Cartesian products have low performance because the number of rows
grows geometrically with the number of tables. The complexity of the
WHERE clause might affect performance or make readability harder.
There are three types of joins: inner, outer and cross join.
The above book excerpt is from:
Super SQL
Server Systems
Turbocharge Database Performance with C++ External Procedures
ISBN:
0-9761573-2-2
Joseph Gama, P. J. Naughter
http://www.rampant-books.com/book_2005_2_sql_server_external_procedures.htm |
