Friday, May 28, 2010

Understanding the Left,Right,Full OUTER JOIN in SQL Server 2008.

 Explain the OUTER JOIN.
In comparison to an inner join, an outer join displays the result set containing all the rows from one table and the matching row from another table.
       Other words, Outer join extends the functionality of inner join. It returns following rows:
•    the same rows as inner join i.e. rows from both tables, which matches join condition and
•    rows from one or both tables, which do not match join condition along with NULL values in place of other table's columns.


Outer join Syntax is below.

   
  Select[column list]

from[ left joined table]                                        
Left! Right! Full [OUTER]  join  [right joined table]            
   on [join condition]

 NOTE: - Only one of the keywords left, right, full can be provided but exactly one is required. Keyword OUTER sometimes is avoided, but anyway keywords left, right or full indicate it is outer join. After the keyword ON join condition is written, generally it can contain many predicates connected with Boolean AND, OR, NOT.  

 Describe the Left outer join. 

A left outer join returns all rows from the table specified on the left side of the LEFT OUTER JOIN keyword and the matching rows from the table specified on the right side. The rows in the table specified on the left side for which matching rows are not found in table specified on the right side, NULL values are displayed in the columns that get data from the table specified on the right side.
                  Other words, Left outer join will output all rows from left input sets based on specified join predicate, even though rows from left input sets doesn’t necessarily have its match at right input sets.
 

Query of LEFT OUTER JOIN

CREATE TABLE customer


(custid CHAR(3) NOT NULL PRIMARY KEY,
firstname VARCHAR(10) NOT NULL);

INSERT INTO customer (custid, firstname) VALUES ('ant','anton');
INSERT INTO customer (custid, firstname) VALUES ('rng','rangga');
INSERT INTO customer (custid, firstname) VALUES ('joh','johan');
INSERT INTO customer (custid, firstname) VALUES ('Har','Harry');

CREATE TABLE sales


(salesID INT NOT NULL PRIMARY KEY,
custid CHAR(3) NULL,
qty    INT);

INSERT INTO sales (salesID, custid, qty) VALUES (1,'ant',10);
INSERT INTO sales (salesID, custid, qty) VALUES (2,'rng',20);
INSERT INTO sales (salesID, custid, qty) VALUES (3,'rng',25);
INSERT INTO sales (salesID, custid, qty) VALUES (4,'smi',10);

command in LEFT OUTER JOIN

 SELECT c.custid, c.firstname, s.salesID, s.custid, qty
FROM customer AS c
LEFT OUTER JOIN sales AS s
ON c.custid = s.custid

The result is displayed below with LEFT OUTER JOIN the both table customer and sales.



Explain the Right outer join.
A right outer join returns all the rows from the table specified on the right side of the RIGHT OUTER JOIN keyword and the matching rows from the table specified on the left side.
               A right outer join is the reverse of a left outer join. All rows from the right table are returned. Null values are returned for the left table any time a right table row has no matching row in the left table.

Query of RIGHT OUTER JOIN
CREATE TABLE customer

(custid CHAR(3) NOT NULL PRIMARY KEY,

 firstname VARCHAR(10) NOT NULL);

INSERT INTO customer (custid, firstname) VALUES ('ant','anton');
INSERT INTO customer (custid, firstname) VALUES ('rng','rangga');
INSERT INTO customer (custid, firstname) VALUES ('joh','johan');
INSERT INTO customer (custid, firstname) VALUES ('Har','Harry');

CREATE TABLE sales


(salesID INT NOT NULL PRIMARY KEY,
custid CHAR(3) NULL,
qty    INT);

INSERT INTO sales (salesID, custid, qty) VALUES (1,'ant',10);
INSERT INTO sales (salesID, custid, qty) VALUES (2,'rng',20);
INSERT INTO sales (salesID, custid, qty) VALUES (3,'rng',25);
INSERT INTO sales (salesID, custid, qty) VALUES (4,'smi',10);


command in RIGHT OUTER JOIN
SELECT c.custid, c.firstname, s.salesID, s.custid, qty
FROM customer AS c
RIGHT OUTER JOIN sales AS s
ON c.custid = s.custid

The result is displayed below with RIGHT OUTER JOIN the both table customer and sales.

 Describe the FULL OUTER JOIN.
 

A full outer join is a combination of left outer join and right outer join. This join returns all the matching and non-matching row from both the tables. However, the matching records are displayed only once. In case of non-matching rows, a NULL value is displayed for the columns for which data is not available.
             A full outer join returns all rows in both the left and right tables. Any time a row has no match in the other table, the select list columns from the other table contain null values. When there is a match between the tables, the entire result set row contains data values from the base tables.


Query of FULLOUTER JOIN
CREATE TABLE customer

(custid CHAR(3) NOT NULL PRIMARY KEY,

 firstname VARCHAR(10) NOT NULL);

INSERT INTO customer (custid, firstname) VALUES ('ant','anton');
INSERT INTO customer (custid, firstname) VALUES ('rng','rangga');
INSERT INTO customer (custid, firstname) VALUES ('joh','johan');
INSERT INTO customer (custid, firstname) VALUES ('Har','Harry');

CREATE TABLE sales


(salesID INT NOT NULL PRIMARY KEY,
custid CHAR(3) NULL,
qty    INT);

INSERT INTO sales (salesID, custid, qty) VALUES (1,'ant',10);
INSERT INTO sales (salesID, custid, qty) VALUES (2,'rng',20);
INSERT INTO sales (salesID, custid, qty) VALUES (3,'rng',25);
INSERT INTO sales (salesID, custid, qty) VALUES (4,'smi',10);


command in FULL OUTER JOIN
SELECT c.custid, c.firstname, s.salesID, s.custid, qty
FROM customer AS c
Full OUTER JOIN sales AS s
ON c.custid = s.custid

The result is displayed below with FULL OUTER JOIN the both table customer and sales.


Facts about LEFT and RIGHT JOIN:-
        How much row is returned  when two tables are left  or Right joined?
        
         When A is Left Joined with B on Column X and Y
         The Records should Be ALL THE Records from A and common records from B.
          But if Duplicates exist in A.
         You will get records duplicated.
        EG 5 Rows in A and 3 Rows in B  you will get 5 rows.
        But 5 Rows + the Duplicates * N  where n is the number of duplicates encase of duplicates in B or A.

        So If some one ask's a question on Left Join on How much row will be returned you will have to give both the answer with explanation or he will consider you wrong because most people know the Exact Row number from A.

          

1 comment:

Abhishek Goel said...

When I write Query Like
Select *
from Appuser A
left outer join Department D on A.department_id=D.row_id and A.is_admin=1
Then is_admin criteria not work
If I write Query Like
Select *
from Appuser A
left outer join Department D on A.department_id=D.row_id
where A.is_admin=1
then works so
I ask that why first query is not working and what difference in between.

Recent Posts