efficient JOIN query

Please help me with the efficient JOIN query to bring the below result :

create table pk1(col1 int)

create table pk2(col1 int)

create table pk3(col1 int)

create table fk(col1 int, col2 int NOT NULL, col3 int, col4 int)

insert into pk1 values(1)
insert into pk1 values(2)
insert into pk1 values(3)

insert into pk2 values(1)
insert into pk2 values(2)
insert into pk2 values(3)

insert into pk3 values(1)
insert into pk3 values(2)
insert into pk3 values(3)

insert into fk values(1, 1, null, 10)
insert into fk values(null, 1, 1, 20)
insert into fk values(1, 1,null, 30)
insert into fk values(1, 1, null, 40)
insert into fk values(1, 1, 1, 70)
insert into fk values(2, 3, 1, 60)
insert into fk values(1, 1, 1, 100)
insert into fk values(2, 2, 3, 80)
insert into fk values(null, 1, 2, 50)
insert into fk values(null, 1, 4, 150)
insert into fk values(5, 1, 2, 250)
insert into fk values(6, 7, 8, 350)
insert into fk values(10, 1, null, 450)

Below query will give the result :

select fk.* from fk inner join pk1 on pk1.col1 = fk.col1 inner join pk2 on pk2.col1 = fk.col2 inner join pk3 on pk3.col1 = fk.col3

Result :
+------+------+------+------+
| col1 | col2 | col3 | col4 |
+------+------+------+------+
| 1 | 1 | 1 | 70 |
| 2 | 3 | 1 | 60 |
| 1 | 1 | 1 | 100 |
| 2 | 2 | 3 | 80 |
+------+------+------+------+

But I require also the NULL values in col1 and col3

Hence doing the below :

select distinct fk.* from fk inner join pk1 on pk1.col1 = fk.col1 or fk.col1 is null inner join pk2 on pk2.col1 = fk.col2 inner join pk3 on pk3.col1 = fk.col3 or fk.col3 is null

+------+------+------+------+
| col1 | col2 | col3 | col4 |
+------+------+------+------+
| null | 1 | 1 | 20 |
| null | 1 | 2 | 50 |
| 1 | 1 | null | 10 |
| 1 | 1 | null | 30 |
| 1 | 1 | null | 40 |
| 1 | 1 | 1 | 70 |
| 1 | 1 | 1 | 100 |
| 2 | 2 | 3 | 80 |
| 2 | 3 | 1 | 60 |
+------+------+------+------+

The above is the reqd output, but the query will be very slow if there are more NULL valued rows in col1 and col3, since I need to also use distinct if I use 'IS NULL' check in JOIN.

Please let me know if there is an aliternative to this query which can return the same result set in an efficient manner.

Answer this question

efficient JOIN query

Peetzore
SELECT DISTINCT fk.col1, fk.col2, fk.col3, fk.col4
FROM fk FULL OUTER JOIN
pk1 ON fk.col1 = pk1.col1 FULL OUTER JOIN
pk3 ON fk.col3 = pk3.col1 FULL OUTER JOIN
pk2 ON fk.col2 = pk2.col1
tigreye007
Hi

The

INNER JOIN ... ON <field1> = <field2> OR <field1> IS NULL

can be replaces with

LEFT OUTER JOIN .... ON <field1> = <field2>

And an INNER JOIN needs to precede all other joins , so the resulting SELECT will look like :

select distinct fk.*
from fk
inner join pk2
on k2.col1 = fk.col2
LEFT OUTER JOIN pk1
on pk1.col1 = fk.col1
LEFT OUTER JOIN pk3
on pk3.col1 = fk.col3
Mark.OSVATH
But the LEFT JOIN does not give the expected result(i,.e 9 rows) I have posted, it gives 12 rows which is not the expected result.
kskwon
Example of using where clause to filter.

SELECT     fk.col1, fk.col2, fk.col3, fk.col4
FROM         fk FULL OUTER JOIN
                      pk1 ON fk.col1 = pk1.col1 FULL OUTER JOIN
                      pk3 ON fk.col3 = pk3.col1 FULL OUTER JOIN
                      pk2 ON fk.col2 = pk2.col1
WHERE     (fk.col1 IS NULL) OR
                      (fk.col3 IS NULL)
efficient JOIN query

Answer this question