Understanding SQL Join Statements

Overview

This article is to explain how JOIN statements are written using SQL in MicroSoft Studio Manager application. When comparing data with more than one table, you use a JOIN. An SQL JOIN clause combines records from two or more tables in a database. A JOIN clause combines fields (columns) from each table that shares a common value. An SQL programmer writes a JOIN statement to display the records for joining. The combined data can then be used a record set or stored in a temporary table.

There are four types of JOIN: INNER, OUTER, LEFT, and RIGHT. Lets work with using these statements using the two tables below:

Model table Car table

In the above tables the CarID column of the Car table (which can be designated as Car.CarID) is the primary key, while CarID of the Model table is a foreign key, or model.CarID. Notice that the BMW has no value assigned (NULL) and that Lexus does not have any models assigned to it. Using SSMS, The SQL statement below will compile and create the following tables in the database:



CREATE TABLE Car

(

CarID INT,

CarName VARCHAR(20)

);

CREATE TABLE model

(

modelName VARCHAR(20),

CarID INT

);

INSERT INTO car(CarID, CarName) VALUES(1,'Acura');

INSERT INTO car(CarID, CarName) VALUES(2, 'Honda');

INSERT INTO car(CarID, CarName) VALUES(3, 'Ford');

INSERT INTO car(CarID, CarName) VALUES(4, 'Lexus');

INSERT INTO model (modelName, CarID) VALUES('TL', 1)

INSERT INTO model (modelName, CarID) VALUES('Civic', 2)

INSERT INTO model (modelName, CarID) VALUES('Accord',2)

INSERT INTO model (modelName, CarID) VALUES('Escape',3)

INSERT INTO model (modelName, CarID) VALUES('Explorer',3)

INSERT INTO model (modelName, carID) VALUES('335i', NULL);

CROSS JOIN

CROSS JOIN returns the Cartesian product of rows from tables in the join. In other words, it will produce rows which combine each row from the first table with each row from the second table. Multiply the number of rows for each table (6 x 4) and that is the number of records (24) displayed in the result.


SELECT * FROM model CROSS JOIN car

Or

SELECT * FROM model, car

INNER JOIN

Inner join creates a new result table by combining column values of two tables (A and B) based upon the join-predicate. The query compares each row of A with each row of B to find all pairs of rows which satisfy the join-predicate. In other words, The INNER JOIN keyword returns rows when there is at least one match in both tables.

SQL specifies two different syntactical ways to express joins: "explicit join notation" and "implicit join notation". The "explicit join notation" uses the JOIN keyword along with the ON keyword to specify the predicates for the join, as in the following example:


SELECT *
FROM model INNER JOIN car
ON model.CarID= car.CarID

Implicit join notation uses the WHERE clause as a filter predicate:


SELECT *
FROM model, car
WHERE model.CarID = car.CarID;

The result turns out a table like this:

Note that the resulting table has no record of 335i because the CarID has a null value and no reference to the car table. There is also no record of Lexus because there is no CarID associated with it in the model table.

OUTER JOIN

An outer join does not require each record in the two joined tables to have a matching record. The joined table retains each record—even if no other matching record exists. Outer joins subdivide further into left outer joins, right outer joins, and full outer joins, depending on which table’s rows are retained (left, right, or both).

(In this case left and right refer to the two sides of the JOIN keyword.)

LEFT OUTER JOIN

The LEFT JOIN keyword returns all rows from the left table (model_CarID), even if there are no matches in the right table (car_CarID).


SELECT *
FROM model LEFT OUTER JOIN car
ON model.carID = car.carID

RIGHT OUTER JOIN

The RIGHT JOIN keyword returns all the rows from the right table (car_carID), even if there are no matches in the left table (model_CarID)

SELECT *
FROM model RIGHT OUTER JOIN car
ON model.CarID = Car.CarID

FULL OUTER JOIN

FULL OUTER JOIN combines the results of both left and right outer joins and returns all (matched or unmatched) rows from the tables on both sides of the join clause. Where records in the FULL OUTER JOINed tables do not match, the result set will have NULL values for every column of the table that lacks a matching

SELECT *
FROM model FULL OUTER JOIN car
ON model.carID = car.carID

EQUI JOIN

SQL EQUI JOIN performs a JOIN against equality or matching column(s) values of the associated tables. An equal sign (=) is used as comparison operator in the where clause to refer equality.

There are two types of EQUI JOINS:

1) SQL INNER JOIN

2) SQL OUTER JOIN

An SQL non EQUI JOIN is a JOIN condition which makes use of some comparison operator other than the equal sign like >, <, >=, <=.

What is the difference between Equi Join and Inner Join in SQL?

An EQUI JOIN is a join with a join condition that only contains an equality operator. An EQUI JOIN returns only the rows that have equivalent values for the specified columns. An inner join is a join with a join condition that may contain both equality or non-equality.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: