-2

I see below question and I believe it is false but the correct answer is true.

A left outer join on tables CUSTOMER and AGENT, with CUSTOMER being listed first and AGENT being listed second in the SQL command, yields all of the rows in the CUSTOMER table, including those that do not have a matching value in the AGENT table.

Based on above question, it should be below select:

select C.*, A.*
from Customer C
left join Agent A on A.CID = C.CID

So the result of the above Statement is all rows from Customer table + matching values in Customer and Agent table. (My Professor says the True/False question is correct but it seems false).

enter image description here

philipxy
  • 14,867
  • 6
  • 39
  • 83
Majid Rezaie
  • 169
  • 1
  • 1
  • 10
  • 3
    True, with one caveat: if a row in `Customer` has multiple matches in `Agent`, then the row from `Customer` will show up multiple times in the result set, one per matching row. – The Impaler Mar 05 '21 at 21:59
  • 1
    MySQL or (MS)sql-server? – Jens Mar 05 '21 at 22:00
  • The statement is true. Perhaps, if you don't understand the wording, you should be speaking to your tutor further to have them elaborate, rather than posting on a public site to call them wrong. – Thom A Mar 05 '21 at 22:01
  • @TheImpaler so the statment in the question is false, right? – Majid Rezaie Mar 05 '21 at 22:01
  • @Jens , I think it should be same for both Mysql and MSSQL. – Majid Rezaie Mar 05 '21 at 22:02
  • 3
    @MajidRezaie I think it's true, since all rows from Customer will show up. Some of them will show up multiple times, though. – The Impaler Mar 05 '21 at 22:03
  • @Larnu Cannot do, since I had class with him long time ago and found this question in my notes so I got confused – Majid Rezaie Mar 05 '21 at 22:08
  • 1
    True or false, I find it's usefulness as a way to think about joins to be a bit limited. – Strawberry Mar 05 '21 at 23:52
  • 1
    [What is the difference between “INNER JOIN” and “OUTER JOIN”?](https://stackoverflow.com/a/46091641/3404097) PS Follow a published textbook, not random web pages. SO relational DB answers about definitions are also mostly uselessly poor. Note that all the other answers at that link to an answer of mine don't explain exactly what outer join returns & all but one other are unclear & not helpful. Just like your quote. The accepted answer is extremely poor. Read all my comments there. – philipxy Mar 06 '21 at 03:47
  • 1
    What is the legend for that diagram? How do we read it? If it's a Venn diagram, what are the kinds of sets & elements & what are the circles & labels & shading? What exactly does that one say? Especially since SQL tables are bags not sets. Those Venn(-like) diagrams for outer vs inner join are misleading & unhelpful. [CROSS JOIN vs INNER JOIN in SQL](https://stackoverflow.com/a/25957600/3404097) [Venn Diagram for Natural Join](https://stackoverflow.com/a/55642928/3404097) – philipxy Mar 06 '21 at 03:51
  • 1
    "yields all of the rows in the CUSTOMER table" A non-natural left join doesn't return *any* row of values that is a row of values in an input table. But the rows returned *contain subows* like the rows in the input tables. So you see how "yields" is not clear? This sort of incomplete & unclear writing is everywhere in DB culture. – philipxy Mar 06 '21 at 17:37

1 Answers1

1

If you try it you will easily see that the statement quoted is TRUE. When no matching row exists in the agent table, the customer row is still shown e.g. Customer 2 below.

declare @Customer table (id int, [Name] varchar(32));
declare @Agent table (id int, CustomerId int, [Name] varchar(32));

insert into @Customer (id, [Name])
values
(1, 'Test Customer 1'),
(2, 'Test Customer 2');

insert into @Agent (id, CustomerId, [Name])
values
(1, 1, 'Test Agent 1');

select C.id [Customer id], C.[Name] [Customer Name], A.id [Agent id], A.CustomerId [Agent Customer id], A.[Name] [Agent Name]
from @Customer C
left join @Agent A on A.CustomerId = C.id;

Results

Customer id Customer Name Agent id Agent Customer id Agent Name
1 Test Customer 1 1 1 Test Agent 1
2 Test Customer 2 NULL NULL NULL

And as The Impaler points out, you can get duplicate Customer rows if there exist multiple Agents per customer e.g. if you append the following SQL to that which we already have:

insert into @Agent (id, CustomerId, [Name])
values
(2, 1, 'Test Agent 2');

You get

Customer id Customer Name Agent id Agent Customer id Agent Name
1 Test Customer 1 1 1 Test Agent 1
1 Test Customer 1 2 1 Test Agent 2
2 Test Customer 2 NULL NULL NULL
Dale K
  • 25,246
  • 15
  • 42
  • 71