SQL Query to get the rows not part of join

Question

Hi,

I am having below tables.

create table test(int id,int data1);
create table test1(int id,int data2);

insert into test values(1,1,);
insert into test1 values(2,2);
insert into test1 values(3,3);

insert into test1 values(1,1);

Now I want the rows of test, that don't participate in join. i.e I want rows (2,2) and (3,3). I want to be able to do this in mysql.

I don't want to use inner query because of performance.

Thank you

Answer 1

Without using sub queries (even the EXISTS variety which I love) you'll need to do a left join and grab the records that didn't join, like so:

select a.* from test1 a
left join test b on a.id = b.id and a.data2 = b.data1
where b.id IS NULL

Answer 2

Using LEFT JOIN/IS NULL:

   SELECT t1.*
     FROM TEST1 t1
LEFT JOIN TEST t ON t.id = t1.id
                AND t.data1 = t1.data2
    WHERE t.id IS NULL

Assuming the columns being joined on, this is the fastest/most efficient method on MySQL. Otherwise, NOT IN/NOT EXISTS are better choices.

Using NOT EXISTS:

SELECT t1.*
  FROM TEST1 t1
 WHERE NOT EXISTS(SELECT NULL
                    FROM TEST t
                   WHERE t.id = t1.id
                     AND t.data1 = t1.data2)

Answer 3

Perhaps something with the union?

select * from test as a
   left outer join test1 as o on a.id = o.id
union all
select * from test as a
   right outer join test1 as o on a.id = o.id
where a.id is null;

I assume what you want to achieve if an exclusive join.
http://www.xaprb.com/blog/2005/09/23/how-to-write-a-sql-exclusion-join/