Monday, March 26, 2012

OUTER JOIN issue

Hi all,
I have two tables (for example, table1, table2) where table1 holds thesame data as table2 but also has other rows that are no contained intable2.
Now if I performed the following query...
SELECT table1.name
FROM table1
LEFT OUTER JOIN table2 ON (RTRIM(LOWER(table1..table_name)) = RTRIM(LOWER(table2.table_name)))

... I expect to get the rows that are not contained in both tables. Butfor some reason I don't get this. I get all the rows from tabel1.
Is my thinking of what theLEFT OUTER JOIN query wrong, or is the query wrong?
To get around my problem, I had to do the following
SELECT table1.name
FROM table1
WHERE table1.table_name not IN (SELECT table_name FROM table2)

I would have preferred to have solved this with theLEFT OUTER JOIN though
Thanks
Tryst
I think you are a little confused about outer joins...
Left/Right Outer joins return all rows from at least one of the tables or views mentioned in the FROM clause, as long as those rows meet any WHERE or HAVING search conditions
Say I have
Table1
Id Name Age
-----
1 a 20
2 b 30
3 c 22
Table2
Id Sex
---
1 M
2 F
Say I want id,name,age,sex(if available) then I would use left join
Select id,name,age,sex from table1 left join table2 on table1.id = table2.id
This will return
Id name age sex
------
1 a 20 M
2 b 30 F
3 c 22 <null>
|||You need to add a WHERE condition to return the results you want from the LEFT OUTER JOIN:
SELECT table1.name
FROM table1
LEFT OUTER JOIN table2 ON (RTRIM(LOWER(table1..table_name)) = RTRIM(LOWER(table2.table_name)))
WHERE table2.table_name IS NULL

What would probably perform better for you is this:
SELECT table1.name
FROM table1
WHERE NOT EXISTS (SELECT NULL FROM table2 WHERE table1.table_name = table2)

Check both execution plans/execution time in Query Analyzer to see which one performs better.

|||Ok, thanks guys.
Why is the inner SELECT query selecting NULL from table2? Whats it use?
Tryst
|||

Tryst wrote:


Why is the inner SELECT query selecting NULL from table2? Whats it use?


The list of column names is immaterial for the EXISTS. What isimportant is the WHERE condition. I have a habit of using NULL,partly because I read somewhere that it uses less resources than a * ora specific column (I am not sure whether or not that is true).

No comments:

Post a Comment