CODEWITHSUNDEEP
sqlfull-outer-join
WesternGun
WesternGun

Reputation: 12728

SQL full outer join - when I want to exclude intersection part, why equal is used in join condition while values are never equal?

I have rows either in table t1 or in table t2. Both table have the common field id and name, and id is either null for t1 or t2(it cannot be in both table at the same time). Now I want to select all rows in both tables, either in t1 or in t2, as long as name matches a pattern.

create table t1 (name varchar(20), id integer);
create table t2 (name varchar(20), id integer);
insert into t1 (name, id) values ('Ellen Joe', 1);
insert into t2 (name, id) values ('Shishiron', 2);
insert into t1 (name, id) values ('Watame', 3);
insert into t2 (name, id) values ('Lamy', 4);

Then I am using now an full (outer) join like this:

select 
  *
from
  t1
full outer join
  t2
on
  t1.id = t2.id
where
  t1.name like '%Joe%'
  and
  t2.id is null
or
  t2.name like '%Joe%'
and
  t1.id is null
;

        NAME    ID    NAME    ID
____________ _____ _______ _____
Ellen Joe        1

The result is what I want. But: why the join condition uses = while the values are never equal? Because when one is null the other is never null. When I use t1.id != t2.id nothing is returned, even syntaxically this is allowed:

SQL> select *
  2  from
  3    t1
  4  full outer join
  5    t2
  6  on
  7    t1.id != t2.id
  8  where
  9    t1.name like '%Shishiron%'
 10    and
 11    t2.id is null
 12  or
 13    t2.name like '%Shishiron%'
 14    and
 15    t1.id is null
 16* ;

no rows selected

I read that the join condition can be anything, just like in WHERE clause. (From MS MYSQL reference). But if it is used for filtering somehow, I don't understand why it's like this in my case.

Demo: https://sqlfiddle.com/oracle/online-compiler?id=7df270bd-78d4-422e-8661-58058e06d81f

Upvotes: 0

Views: 115

Answers (2)

Andrew
Andrew

Reputation: 8693

The intent of this query is to limit the rows in the two table and then do a full join. In this case it works, but it's ugly and confusing to read. Instead you can use derived tables to apply the where clause constraint, and then join those together.

The query in your fiddle can be re-written like so:

SELECT
*
FROM
(select * from t1 where name like '%Joe%') t1
full outer join (select * from t2 where name like '%Joe%') t2
    on t1.id = t2.id;

Upvotes: 0

Chris Maurer
Chris Maurer

Reputation: 2539

If I take your problem statement at face value, then you should not be joining t1 and t2 tables at all, and especially not on some random id number that never matches.

You can write one query on t1 that returns all records containing "Joe" and write a similar query on t2. The question is how to combine those two queries to produce a single result set? SQL defines a UNION query operator to do exactly that. So,

Select name, 't1' as tablename, id
  From t1
  Where name like '%Joe%'
Union All
Select name, 't2', id
  From t2
  Where name like '%Joe%'

That will identify matching records from both tables and tells you which tables they are in.

Upvotes: 0

Related Questions