-2

What is the difference between these?

The beginning part is as follows:

SELECT p.Name
     , p.DOB
     , ea.mailaddress
  FROM Mall p
  LEFT 
  JOIN mailaddresss ea

And differ both of these ending lines:

ON p.MallEntityID = ea.MallEntityID
WHERE ea.mailaddress LIKE 'NULL'

and

ON p.MallEntityID = ea.MallEntityID 
AND ea.mailaddress LIKE 'NULL'

Sample data is as follows:

name mailaddress Mallentity
Bob                   1
Joe   ""              2
Dib   NULL            3
Shay  8892            4
Strawberry
  • 33,750
  • 13
  • 40
  • 57
Morgan
  • 31
  • 1
  • 6
  • 2
    Actually, there is *NOTHING IN COMMON* between the empty string `""`, the string `'NULL'`, and the SQL (lack of) value "NULL". They're all completely different. `ea.mailaddress LIKE ‘NULL’` belongs in your "WHERE" clause, not the "JOIN". Look here: https://dataschool.com/how-to-teach-people-sql/difference-between-where-and-on-in-sql/ – paulsm4 Feb 19 '21 at 23:03
  • Please don't use Word to format code – Strawberry Feb 19 '21 at 23:08
  • The first query is an inner join. The second is an outer join. `Like 'string'` is the same as `= 'string'`, unless using a case sensitive collation (highly unlikely) – Strawberry Feb 19 '21 at 23:10
  • Incidentally, the test for null is `IS NULL` – Strawberry Feb 19 '21 at 23:11
  • 1
    Does this answer your question? [Difference between NULL and Blank Value in Mysql](https://stackoverflow.com/questions/24444518/difference-between-null-and-blank-value-in-mysql) – devlin carnate Feb 19 '21 at 23:13

1 Answers1

0

The first query will find Malls with or without email addresses that have the string 'null'. The second one won't include them.

The Impaler
  • 45,731
  • 9
  • 39
  • 76