SQL Server

>>Previous>>

Cross Join or Unrestricted Join

Returns combination of all rows of all tables in join as the result set. Each row of one table is joined with each row of another table.

Example:

SQL Server syntax

SELECT p.pub_name, p1.pr_info
FROM publishers p, pub_info p1

ANSI syntax

SELECT p.pub_name,p1.pr_info
FROM publishers p CROSS JOIN pub_info p1


Outer Join

Restricts rows from one table while allowing all rows from another table as a result set. Usually used for orphan records.

Outer joins. Outer joins can be a left, right, or full outer join. Outer joins are specified with one of the following sets of keywords when they are specified in the FROM clause:


LEFT JOIN or LEFT OUTER JOIN

The result set of a left outer join includes all the rows from the left table specified in the LEFT OUTER clause, not just the ones in which the joined columns match. When a row in the left table has no matching rows in the right table, the associated result set row contains null values for all select list columns coming from the right table.

Example:

USE pubs
SELECT a.au_fname, a.au_lname, p.pub_name
FROM authors a LEFT OUTER JOIN publishers p
ON a.city = p.city
ORDER BY p.pub_name ASC, a.au_lname ASC, a.au_fname ASC

Note:If Inner Join were used in above query then we would have got only the results of authors name whose city was same as publishers and not those authors who lived in city to which no publisher belonged to. In this case we got name of all the authors.


Self Join

Correlates rows of a table with other rows in the same table.

A table can be joined to itself in a Self Join. For example, you can use a Self Join to find out the authors in Oakland, California who live in the same zip code area.

Because this query involves a Join of the authors table with itself

USE pubs

SELECT au1.au_fname, au1.au_lname, au2.au_fname, au2.au_lname
FROM authors au1 INNER join authors au2
ON au1.zip = au2.zip
WHERE au1.city = 'Oakland'
AND au1.state = 'CA'
AND au1.au_id < au2.au_id

>>>Next>>>