SQL assistance with the WHERE clause. Getting an error for the following code

Question

Hi guys I am trying to create a query to display employee name and employee number along with their super’s name and super’s number. Listing also includes employees who don’t have any supervisor. I wrote a bit of code for it and it gives me and error because its not right. Would anyone be kind enough to give me a hand with this. Cheers guys

SELECT employee.ename, employee.empno, boss.ename, boss.empno
FROM   emp employee, emp boss
WHERE  employee.super = boss.empno 
OR     employee.super AND boss.empno IS NULL;

Thanks in advance

-Jay

Allright guys I found the answer and I used outer join operator. The left join works but we are not taught that in our course. Thanks for that. The answer using the outer join is as follows:

SELECT employee.ename, employee.empno, boss.ename, boss.empno
FROM   emp employee, emp boss
WHERE  employee.super = boss.empno(+);

So it will still return rows that has null for employee.super.

@Steward, left join is not the same as an equi-inner join that joins on `(a=b or (a is null and b is null))` the left join will also produce a a-row _(for want of a better moniker)_ on `a=something and b is null`, the latter join will not. — Johan, Oct 06 '11 at 11:20

Mark Byers · Accepted Answer · 2011-10-06T11:21:20.433

4

Try this:

SELECT employee.ename, employee.empno, boss.ename, boss.empno
FROM emp employee
LEFT JOIN emp boss
ON employee.super = boss.empno

The LEFT JOIN means that every row in the left table (employee) will be returned, even if there is no matching row in the right table (boss).

edited Oct 06 '11 at 11:21

answered Oct 06 '11 at 11:15

Mark Byers

811,555
193
1,581
1,452

Good one bro! works fine but I forgot to add I can only use outer join. So I uploaded my answer after I found it – Jay Oct 06 '11 at 11:29
1

@Jay: `LEFT JOIN` **is** an outer join, you can also use: `LEFT OUTER JOIN`. – ypercubeᵀᴹ Oct 06 '11 at 11:59

Johan · Answer 2 · 2011-10-06T13:58:17.983

1

Do not use implicit SQL '89 join syntax, it's an anti-pattern.
Get out of jurassic park and use explicit SQL '92 join syntax:

SELECT 
  employee.ename
  , employee.empno
  , boss.ename
  , boss.empno 
FROM   emp employee
INNER JOIN emp boss on (employee.super <=> boss.empno)

You can use the <=> operator:

null = null   -> null  -> no join
1 = 1         -> true  -> join
1 = 0         -> false -> no join

null <=> null -> true  -> join
1 <=> 1       -> true  -> join
1 <=> 0       -> false -> no join

<=> is MySQL specific, outside of MySQL you'd use:
(e.super = b.empno or (e.super is null and b.empno is null))

Note that above join is not the same as a left join:

//code from Mark Byers' answer
SELECT employee.ename, employee.empno, boss.ename, boss.empno 
FROM emp employee 
LEFT JOIN emp boss 
ON employee.super = boss.empno

The inner join produces fewer rows.

Disclaimer: The following link is a quick and dirty visualisation of joins, to be taken with a grain of salt. But it does visualize the general idea.
See: http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html

edited Oct 06 '11 at 13:58

answered Oct 06 '11 at 11:14

Johan

74,508
24
191
319

"null = null -> false" -- I'm assuming you've made a misstatement, rather than MySQL having dodgy three-valued logic. In Standard SQL, `null = null` should evaluate to UNKNOWN. – onedaywhen Oct 06 '11 at 13:08
@onedaywhen, It's not a misstatement, for the purpose of the join, the outcome is either join (stated as `true`) or no-join (stated as `false`). Will rephase the answer. – Johan Oct 06 '11 at 13:10
Thanks for the correction. Remaining issues (which I don't expect you will correct :) Old style inner joins are just a matter of taste, not an "anti-pattern". The pictures you linked to I think tend to confuse more than clarify e.g. the first picture depicts `A INTERSECT B`, second picture is `A UNION B`, the third picture is simply `A`, etc. – onedaywhen Oct 06 '11 at 13:18
@onedaywhen, implicit joins are an official anti-pattern. One if's or buts about it. The fact that the machine understands them does not make then useful for humans. The same problem exists with `goto` it's very useful, works like a charm yet still a clear anti-pattern. See this question: http://stackoverflow.com/questions/128965/is-there-something-wrong-with-joins-that-dont-use-the-join-keyword-in-sql-or-mys – Johan Oct 06 '11 at 13:44
@onedaywhen, I would love to have a better link to explain joins, the sloppyness in the article has been bugging me for a while, unfortunatly I don't have a good link that's easy to grok. – Johan Oct 06 '11 at 13:56
"official anti-pattern" -- I'm not sure that you can convince me of that and I'm certainly not convinced by reading that thread. The main thrust of the most popular answer is IMO wrong: most optimizers will recognise the inner join from the old style syntax, as one of the answers demonstrates using SQL Server. Even Bill Karwin, who literally [wrote the book on SQL antipattern](http://pragprog.com/book/bksqla/sql-antipatterns) stops short of condemning the old style syntax in his answer there :) Bottom line: product and restriction are basic relational operations that can't be outlawed. – onedaywhen Oct 06 '11 at 14:12
@onedaywhen, I agree that the accepted answer is actually the worst answer, such is life. It's an anti-pattern because: A: it makes it hard to spot messing join criteria (inner joins make that easy); B: explicit joins force you to group the join criteria per table that you join, with implicit joins you can mix and match making code hard to understand. C: implicit joins put everything in the where clause, agian hindering understanding D: switching between left and inner joins is **hard** with implicit joins but trivial with explicit joins E: see below: – Johan Oct 06 '11 at 14:20
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/4066/discussion-between-johan-and-onedaywhen) – Johan Oct 06 '11 at 14:20

score 1 · Answer 3 · answered Oct 06 '11 at 11:15

You have used employee.super and it is trying to be evaluated as a boolean in:

OR     employee.super AND boss.empno IS NULL;

You need to be comparing it to something.

I would use:

SELECT employee.ename, employee.empno, boss.ename, boss.empno 
FROM   emp employee
LEFT JOIN emp boss ON employee.super = boss.empno

score 0 · Answer 4 · answered Oct 06 '11 at 11:15

Use:

SELECT employee.ename, employee.empno, boss.ename, boss.empno, CASE WHEN boss.empno IS NULL THEN 'no supervisor found'
                                                                    ELSE 'supervisor found'
                                                                END
  FROM employee LEFT JOIN emp boss ON boss.empno = employee.super

score 0 · Answer 5 · answered Oct 06 '11 at 13:22

0

SELECT employee.ename, employee.empno, boss.ename, boss.empno
FROM   emp employee, emp boss
WHERE  employee.super = boss.empno
UNION
SELECT employee.ename, employee.empno, '(no boss)', ('no boss')
FROM   emp employee
WHERE  employee.super IS NULL;

answered Oct 06 '11 at 13:22

onedaywhen

55,269
12
100
138

SQL assistance with the WHERE clause. Getting an error for the following code

5 Answers5