SQL Server

>>Previous>>

Implementing Joins


Joins are used to retrieve data from two or more tables. The results appear as a single table with columns from the entire table specified in the SELECT column_list and meeting the search criteria.

In nutshell Joins connect two or more tables based on a join condition and produce results as new table with the rows that satisfy the join condition


We will probe Joins using both SQL Server syntax and ANSI syntax.


Inner Join: There can be two types of Inner Joins.

One is EquiJoin and other is Natural Join

Using Pubs consider tables:


Publishers (pub_id, pub_name, city, state, country) Primary Key (pub_id)


Pub_info (pub_id, logo, pr_info) Primary Key (pub_id)


Authors (au_id, au_lname, au_fname, phone, address, city, state, zip, contact) Primary Key (au_id)

EquiJoin: In Equi Join Column values are compared for equality and redundant columns are displayed as columns in the result set


Example:

SQL Server syntax

SELECT *
FROM publishers, pub_info
WHERE publishers.pub_id=pub_info.pub_id

ANSI syntax

SELECT *
FROM Publishers
INNER JOIN pub_info ON publishers.pub_id = pub_info.pub_id


Natural Join: In Natural Joins redundant columns are not displayed twice

Example:

SQL Server syntax

SELECT p. *, p1.logo, p1.pr_info
FROM publishers p, pub_info p1
Where p.pub_id = p1.pub_id

ANSI syntax

SELECT p.*, p1.logo, p1.pr_info
FROM publishers p
INNER JOIN pub_info p1 ON p.pub_id = p1.pub_id

>>>Next>>>