SQL convert "left join" with a "from" clause with multiple tables and a "where" condition

Question

Image you have 2 tables like this:

User: with columns ID, NAME and APPARTMENT_ID.

Appartment: with columns ID, ADDRESS.

user.appartment_id is a foreign key for appartment.id.

With a left join I can do something like:

select u.name, a.address from user as u 
    left join appartment as a on u.appartment_id = a.id

And users with appartment_id null will match. If i write a select with this other structure (without using left join) those users won't match.

selct u.name, a.address from user as u, appartment as a
where u.appartment_id = a.id

Is there a way to modify the second query in order to obtain the same result set of the first, but without using left join?

Thanks

No. DON'T use the second query.. Read https://stackoverflow.com/questions/1599050/ansi-vs-non-ansi-sql-join-syntax — Kaushik Nayak, Mar 27 '18 at 15:59
Why don't you want to use left join? Seems the first query is fine — Daniel Marcus, Mar 27 '18 at 16:02
Some DBMSes (like Oracle, DB2 & SQL Server) used to support proprietary syntax for Outer Joins, but it has been deprecated ages ago and using it is strongly discouraged. And the result migth differ from Standard SQL Outer Joins. — dnoeth, Mar 27 '18 at 16:20

score 0 · Answer 1 · answered Mar 27 '18 at 16:04

If you are using Oracle, this is the old syntax for outer joins. You just need to add a (+) on the columns of the second table:

select u.name, a.address
  from user as u, appartment as a
 where u.appartment_id = a.id (+)

You will find this type of syntax a lot on Oracle environments, I guess that's why you asked it. You should probably use the new syntax though

score 0 · Answer 2 · answered Mar 27 '18 at 16:08

0

Using left join is better, but here is an alternative.

select u.name, a.address 
from user as u left join appartment as a on u.appartment_id = a.id
union
select name, null address
from user apartment_id is null

answered Mar 27 '18 at 16:08

Dan Bracuk

20,699
4
26
43

Ricky McMaster · Accepted Answer · 2018-03-27T17:28:26.837

Without a left join you could just use this (assuming that the foreign key constraint has functioned correctly):

SELECT
  u.name,
  a.address
FROM user u
  JOIN appartment a ON u.appartment_id = a.id
UNION
SELECT
  name,
  NULL AS address
FROM user
WHERE apartment_id IS NULL
;

But I'm curious as to why you'd want to do without a left join?

If you suspect that there there is incorrect data entered in user.apartment_id then you could use NOT EXISTS with a sub-query:

SELECT
  u.name,
  a.address
FROM user u
  JOIN appartment a ON u.appartment_id = a.id
UNION
SELECT
  name,
  NULL AS address
FROM user
WHERE
  NOT EXISTS (
      SELECT 1
      FROM appartment
      WHERE appartment.id
            = user.appartment_id)

);

This should cover you for null values and incorrect values.

SQL convert "left join" with a "from" clause with multiple tables and a "where" condition

3 Answers3