Outer Joins

Question

Outer Joins

Data Rat

SSC Eights!

Points: 943
More actions
October 15, 2019 at 8:44 pm
Go to Answer
#3689073
Hi Friends,
I know that we have a left and right outer join that retains the rows of the table based on how we chose to write the query. For example, depending on whether the table is to the left of the join or to the right of the join. My question is, in what situations, or queries do we want to retain the rows of the table with no matching records? Thanks !
- This topic was modified 5 years, 1 month ago by Data Rat .
- This topic was modified 5 years, 1 month ago by Data Rat .
Jeff Moden

SSC Guru

Points: 1004312
More actions
October 15, 2019 at 9:05 pm
Answer
#3689079

When you're doing an "Upsert". That is, you're checking to see if rows already exist or not to know whether you have to do an INSERT or an UPDATE.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 1

An example is below, say you want to query how many orders your customers have, but include in the output customers who don't have any orders at all:

SELECT c.cust_name, COUNT(o.order_id) AS order_count
  FROM dbo.Cusomter c
  LEFT OUTER JOIN dbo.Order o ON c.cust_id = o.cust_id;
  GROUP BY c.cust_name

If you did an INNER JOIN instead of OUTER join, you would only get the customers who had at least 1 order.

Data Rat SSC Eights! Points: 943 More actions · Answer 2

Thanks for your help, but I was wondering if you could maybe structure your answer in a real world example. I think that's why I am struggling with this stuff is because I haven't had an opportunity to see how it works in the real world. I understand it excellent in a text book or theoretical context, but again I get lost when trying to read these long queries or stored procedures someone wrote where I work and they have ZERO comments.

This reply was modified 5 years, 1 month ago by Data Rat .
This reply was modified 5 years, 1 month ago by Data Rat .

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 3

CREATE TABLE #Customer (
    cust_id int identity(1,1) primary key not null,
    cust_name varchar(40));

INSERT INTO #Customer
    VALUES ('Fred'),('Wilma'),('Barney'),('Betty');

CREATE TABLE #Order (
    order_id int identity(1,1) primary key not null,
    cust_id int not null,
    order_date date);

INSERT INTO #Order
    VALUES (1, '2018-05-22'), (1, '2018-08-04'), (1, '2019-04-12'),
           (2, '2019-01-29'),
           (3, '2018-02-10'), (3, '2018-06-01'), (3, '2018-11-15'), (3, '2019-02-02'), (3, '2019-05-11'), (3, '2019-08-21'),
           (4, '2018-07-10');

--query 1
SELECT c.cust_name, COUNT(o.order_id) AS order_count
  FROM #Customer c
  LEFT OUTER JOIN #Order o ON c.cust_id = o.cust_id AND o.order_date >= '2019-01-01'
  GROUP BY c.cust_name;

--query 2
SELECT c.cust_name, COUNT(o.order_id) AS order_count
  FROM #Customer c
  INNER JOIN #Order o ON c.cust_id = o.cust_id AND o.order_date >= '2019-01-01'
  GROUP BY c.cust_name;

Okay I put some data here for you to see. In query 1 with the LEFT OUTER JOIN, then Betty shows up with order_count of 0 since she doesn't have any orders this year. If you use INNER JOIN like in query 2, then Betty doesn't show up in the results at all even though she is still in the Customer table.