When we call the Left/Right Outer Join, what Left/Right means?

From where left/right does it mean?

For example,

SELECT C.custid, C.companyname, O.orderid
FROM Sales.Customers AS C
   LEFT OUTER JOIN Sales.Orders AS O
      ON C.custid = O.custid;

What is left table, what is right table?

The first table used is called left table and second one is right table. If you tale example of the query you posted customer table would be left table and order would be right one. Above is without any query hint used

Please spend some time reading about joins you would find lot of article on in

This query will select all of the records from Sales.Customers (as C) - which is the left table. For any record in Sales.Customer, if there is a record in Sales.Orders that matches the selected Customer record, then that Order record will be selected. the Order table is the Right table.

Please note that there will be at least one record for the Customer. If there is more than one Order record, then the Customer record will be included multiple times - once for each Order.

Consider three customers - A, B, and C. A has no Orders, B has one Order, and C has three orders.

A will appear once, with no orderid.

B will appear once, with the one orderid.

C will appear three times, each with a different orderid (each will be one of the three).

Hope this helps!

• Marked as answer by dy0803 6 hours 45 minutes ago

The keywords LEFT OUTER JOIN make the difference. The table mentioned on the LEFT side of the keywords act as the OUTER table. This means that all rows from the LEFT table will be returned by the query. Only matching rows from the RIGHT table will be returned.

Demo queries -

create table a (id int, col1 varchar(10));
create table b (id int, col1 varchar(10));

insert into a (id, col1) values
(1, 'a'), (2, 'b'), (3, 'c');

insert into b (id, col1) values
(2, 'b'), (3, 'c'), (4, 'd');

-- all values
select a.*, b.* 
from a full outer join b
on a.id = b.id

-- Query 1
-- all values from a
-- only matching from b
select a.*, b.* 
from a left outer join b
on a.id = b.id

-- Query 2
-- switching the columns in ON CLAUSE
-- same results as Query 1
-- all values from a
-- only matching from b
select a.*, b.*
from a left outer join b
on b.id = a.id -- switching these. But does not matter.

-- Query 3
-- switching the tables in JOIN CLAUSE
-- different results than Query 1 and 2
-- all values from b
-- only matching from a
select a.*, b.*
from b left outer join a -- switching these. Data changes.
on a.id = b.id -- this does not matter

Results look like these -

I could understand by this simple example.

If so, the record count of 

select a.*, b.* 
from a left outer join b
on a.id = b.id

is always the same as the record count of

select * from a 

And the record count of

select a.*, b.* 
from a right outer join b
on a.id = b.id

is always the same as the record count of

select * from b

Is this true?

• Edited by dy0803 4 hours 49 minutes ago

In most cases it will be true, unless you don't count on a column from the INNER table i.e. don't count on a column in table b in this case. Remember that COUNT function ignores NULL values.

select COUNT(*) from a -- gives 3

select COUNT(*) -- gives 3
from a left outer join b
on a.id = b.id

select count(a.id) -- gives 3
from a left outer join b
on a.id = b.id

select count(b.id) -- gives 2 because ignores NULL in b.id
from a left outer join b
on a.id = b.id

Another demo -

declare @t table (id int)
insert into @t values (1), (2), (null), (10)

select COUNT(*) from @t -- gives 4
select COUNT(id) from @t -- gives 3