1

Here is the query:

SELECT a.name as Employee, b.name as Manager from Employee as a
LEFT JOIN Employee as b
ON a.ManagerId = b.Id AND a.ManagerId IS NOT NULL
WHERE 1

It works on this table:

+----+-------+--------+-----------+
| Id | Name  | Salary | ManagerId |
+----+-------+--------+-----------+
| 1  | Joe   | 70000  | 3         |
| 2  | Henry | 80000  | 4         |
| 3  | Sam   | 60000  | NULL      |
| 4  | Max   | 90000  | NULL      |
+----+-------+--------+-----------+

As Idea this query shold show all Employers. A set of rows that have a.ManagerId = b.Id.

And everthing works fine, exept rows

Sam, NULL Max, NULL

Why do these rows returns. If I set a.ManagerId is NOT NULL in the ON clause.

Dmitry Dmitriev
  • 929
  • 8
  • 23
  • NULL is a strange beast. That's why. – Strawberry Oct 21 '19 at 16:34
  • 1
    I think you can reference here for more clearer about the difference between "on" & "where" phase for joined condition. https://stackoverflow.com/questions/354070/sql-join-where-clause-vs-on-clause – momo Oct 21 '19 at 16:44
  • Not because of NULL, but because of how do the 'ON' works. 'ON' give all A rows connected with B rows by some rule. Anyway all **A** rows will be presented in the answer. Next to filter A rows, need to use 'WERE'. In this case add a rule for `ON` it did not attach B rows for case where ManagerID is NULL, but it does not reject this rows. – Dmitry Dmitriev Oct 21 '19 at 16:45
  • 2
    Whatever you write in the ON clause, you will get all rows from `a`. That's how LEFT JOIN works. – Paul Spiegel Oct 21 '19 at 16:55
  • @PaulSpiegel more correct to say, at least ALL rows form **A** will be used. In case **ON FALSE**, in case **ON TRUE** will be A rows B times. – Dmitry Dmitriev Oct 21 '19 at 16:57
  • Because that's how left join works. You are just asking how left join works, which you should research before asking & is a faq. If you want to know why your expectatinons are not met you need to tell us your expectations. Please don't ask for yet another presentation, tell us where you are stuck in (an authoritative) one. See [ask], other [help] links & the voting arrow mouseover texts. – philipxy Oct 21 '19 at 21:05
  • If `a.ManagerId = b.Id` then `a.ManagerId IS NOT NULL`--SQL `x=y` means "x equals y & x is not NULL & y is not NULL". So there is no point in adding `AND a.ManagerId IS NOT NULL` to `a.ManagerId = b.Id`. Learn how NULL is used. (In functions, conditions, WHERE, constraints, OUTER JOINs, "unique", "duplicate", UNIQUE, FKs, etc--an irregular pile of special cases.) – philipxy Oct 22 '19 at 06:29

2 Answers2

0
 a.Manager IS NOT NULL

is referrere to an alias of table Employee a instead the NULL value are referred the the alias table Employee b

An alias for a table is used as the related rows own to a new separated table

In sql this is tipical when you need values from an original table for different use and join

You should think at table row as sets of datas and an alias is a separated set of the same data

ScaisEdge
  • 131,976
  • 10
  • 91
  • 107
-1

When wrote a query

SELECT x from A
LEFT JOIN B
ON  A.b_id = b.id

All table A is selected with connection to B. But All A is used.

In the case of example A and B tables are the same, but nevertheless. We have full list of A table, where to each row one row of B table is attached, or not attached.

So to reduce amount rows of A tables needs to control it with WERE CLAUSE

The correct query will be

SELECT a.name as Employee, b.name as Manager from Employee as a
LEFT JOIN Employee as b
ON a.ManagerId = b.Id  
WHERE a.ManagerId IS NOT NULL
  1. ON clause used to define rules what rows attach to A table
  2. WERE clause filters rows from step 1. In other words "cut" some rows from A table, in this example.

When I use ON FAlSE: It just select all rows from A, and do not concatenate with B rows.

SELECT a.name as Employee from Employee as a
LEFT JOIN Employee as b
ON false
WHERE 1
  • Joe
  • Henry
  • Sam
  • Max

When I use ON TRUE it use ALL A rows B times.

SELECT a.name as Employee from Employee as a
LEFT JOIN Employee as b
ON true
WHERE 1

{"headers": ["Employee"], "values": 
[
["Joe"], ["Henry"], ["Sam"], ["Max"], 
["Joe"], ["Henry"], ["Sam"], ["Max"],
["Joe"], ["Henry"], ["Sam"], ["Max"], 
["Joe"], ["Henry"], ["Sam"], ["Max"]]}
Dmitry Dmitriev
  • 929
  • 8
  • 23
  • Can you clarify, **LEFT JOIN ON** without **INNER or OUTER** prefix - is an **INNTER** or **OUTER** by default? – Dmitry Dmitriev Oct 22 '19 at 05:23
  • There is no INNER LEFT/RIGHT/FULL JOIN ON--those are the 3 OUTER JOINs. LEFT/RIGHT/FULL JOIN ON is LEFT/RIGHT/FULL OUTER JOIN ON. JOIN ON without INNER or LEFT or RIGHT or FULL is INNER JOIN ON. Comma means CROSS JOIN but has lower precedence than keyword JOINs. Also, INNER/LEFT/RIGHT/FULL JOIN ON 1=1 are all CROSS JOIN. (MySQL allows (INNER) JOIN without ON to mean CROSS JOIN; but that is non-standard. MySQL does not have FULL (OUTER) JOIN ON). Read an authoritative description--a textbook or [manual](https://dev.mysql.com/doc/refman/8.0/en/join.html). – philipxy Oct 22 '19 at 05:54
  • Pardon me if I already posted the following: What is the point of this post? It states a bunch of things, but what do they have to do with the question? If this text is just additional question, please edit it into the question post instead. Also, most of this is very unclear. Also, it tries to say some things about some operator calls but it certainly does not clearly describe how those operators work. – philipxy Oct 22 '19 at 06:07
  • I made a mistake in an earlier comment, when I said that you turn inner join into outer join. You don't. You have a LEFT JOIN b ON c WHERE a.ManagerId IS NOT NULL. That WHERE does not require any b columns to be not null. (The WHERE removes rows from the outer join that have null a.ManagerId. For that c that will be one row in the output per row in table a with null ManagerId.) – philipxy Oct 22 '19 at 07:00