1

I am new to SQL. And I am practicing SQL questions on leetcode.

This is the question: https://leetcode.com/problems/combine-two-tables/

Why can't I use:

select Person.FirstName, Person.LastName, Address.City, Address.State
from Person,Address
where Address.PersonId=Person.PersonId

It failed in this case:

Input:
{"headers": {"Person": ["PersonId", "LastName", "FirstName"], "Address": ["AddressId", "PersonId", "City", "State"]}, "rows": {"Person": [[1, "Wang", "Allen"]], "Address": []}}
Output:
{"headers": ["FirstName", "LastName", "City", "State"], "values": []}
Expected:
{"headers": ["FirstName", "LastName", "City", "State"], "values": [["Allen", "Wang", null, null]]}

The accepted answer is:

select Person.Firstname, Person.lastname, Address.city, Address.state
from Person
left join Address on Person.PersonId = Address.PersonId

Why is this? I thought both of the queries would accomplish the same thing and have the same effect.

ElGavilan
  • 6,610
  • 16
  • 27
  • 36
hidemyname
  • 3,791
  • 7
  • 27
  • 41

2 Answers2

3

Because of the below reson.

enter image description here

While --> where clause creates an implicit inner join

enter image description here

prashant thakre
  • 5,061
  • 3
  • 26
  • 39
2

The question states

Write a SQL query for a report that provides the following information for each person in the Person table, regardless if there is an address for each of those people:

Notice the last part says regardless if there is an address. So if you use a WHERE clause you will not be getting results for entries that do not contain an address. On the other hand, a join a left join will combine both tables despite whether or not there is an entry in the Address table for a given Person.

barbiepylon
  • 891
  • 7
  • 23
  • 2
    _On the other hand, a join will combine both tables_ should be stated as _On the other hand, a **LEFT** join will combine both tables_ – Michael Berkowski Apr 28 '15 at 17:28
  • I felt like a complete dingus after reading this; so obvious. Thank you kindly stranger! – j9000 Jan 17 '20 at 15:35