| Author |
Topic |
|
vladimir_grigoro
Yak Posting Veteran
62 Posts |
 Posted - 2002-02-20 : 02:56:02
|
| How to SELECT only rows where result is different from NULL when I performed OUTER JOIN (LEFT or RIGHT)? |
|
|
Nazim
A custom title
1408 Posts |
Posted - 2002-02-20 : 03:02:43
|
| Your Question is too vague. which results are you talking about (i take it as some field value, though am sure you are looking for something else).select a.coulmn1,b.column2 from a left outer join bon a.commonkey=b.commonkeywhere a.somefield is not nullif this isnt what you want ,post your actual question . with some sample data and the results you expectHTH-------------------------------------------------------------- |
 |
|
|
vladimir_grigoro
Yak Posting Veteran
62 Posts |
Posted - 2002-02-20 : 03:07:33
|
Yes, I mean exactly that but is there any way to perform this query without WHERE clause?quote:
Your Question is too vague. which results are you talking about (i take it as some field value, though am sure you are looking for something else).select a.coulmn1,b.column2 from a left outer join bon a.commonkey=b.commonkeywhere a.somefield is not nullif this isnt what you want ,post your actual question . with some sample data and the results you expectHTH--------------------------------------------------------------
|
|
|
|
Nazim
A custom title
1408 Posts |
Posted - 2002-02-20 : 03:11:35
|
| How about removing where clause and adding a And on join conditionselect a.coulmn1,b.column2 from a left outer join bon a.commonkey=b.commonkeyand a.somefield is not nullHTH-------------------------------------------------------------- |
|
|
|
vladimir_grigoro
Yak Posting Veteran
62 Posts |
Posted - 2002-02-20 : 03:15:06
|
| Thanks HTH,That's a good idea but it is the same I just wonder if there any type of OUTER JOIN to perform this type of query as SQL Server to do this automatically without such clause as WHERE or similar type of syntaxsis? |
|
|
|
Nazim
A custom title
1408 Posts |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-02-20 : 04:01:35
|
| Why do you ask this question?What are you trying to do?==========================================Cursors are useful if you don't know sql.Beer is not cold and it isn't fizzy. |
|
|
|
vladimir_grigoro
Yak Posting Veteran
62 Posts |
Posted - 2002-02-21 : 02:37:26
|
I am trying to get all data from TABLE A which is not in TABLE B.quote:
Why do you ask this question?What are you trying to do?==========================================Cursors are useful if you don't know sql.Beer is not cold and it isn't fizzy.
|
|
|
|
Nazim
A custom title
1408 Posts |
Posted - 2002-02-21 : 02:47:28
|
| thought this query should help you instead of joins in doing what you wantselect * from TableA where somekey not in ( select somekey from tableb)-------------------------------------------------------------- |
|
|
|
vladimir_grigoro
Yak Posting Veteran
62 Posts |
Posted - 2002-02-21 : 08:26:36
|
I know but this query is too slow and the performance is very low:( I am trying to find the fastest solution. I am dealing with a databases with a more than 200 millions of records:)quote:
thought this query should help you instead of joins in doing what you wantselect * from TableA where somekey not in ( select somekey from tableb)--------------------------------------------------------------
|
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-02-21 : 08:31:15
|
| The OUTER JOIN with the WHERE clause will most likely be the fastest performing solution, if the columns in both tables are indexed. 200 million rows is a lot to process no matter what solution you use, you can't expect it to run in 60 seconds. How slow is it exactly anyway?Edited by - robvolk on 02/21/2002 08:31:59 |
|
|
|
vladimir_grigoro
Yak Posting Veteran
62 Posts |
Posted - 2002-02-21 : 08:36:11
|
It depends on the level of difference between both tables. The time varies between 30 min. to 6-7 hours. Do you have any idea for better speed?quote:
The OUTER JOIN with the WHERE clause will most likely be the fastest performing solution, if the columns in both tables are indexed. 200 million rows is a lot to process no matter what solution you use, you can't expect it to run in 60 seconds. How slow is it exactly anyway?Edited by - robvolk on 02/21/2002 08:31:59
|
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-02-21 : 08:51:32
|
| Add indexes to both tables, on the columns that they JOIN on. If you already have indexes on these columns, perform an UPDATE STATISTICS on these tables/indexes. You may also want to try a DBCC DBREINDEX on these tables, your performance problems could be from data fragmentation from a lot of INSERT/DELETE activity, and DBCC DBREINDEX will help.These could take a lot of time to run (several hours) so you should only do it during a maintenance window; after your backups are performed, for example. |
|
|
|
vladimir_grigoro
Yak Posting Veteran
62 Posts |
Posted - 2002-02-21 : 08:55:41
|
The columns are already indexed with UNIQUE, CLUSTERED, FILL FACTOR=90, WITH PAD INDEX is it OK?Thanks for the advise I'll try.quote:
Add indexes to both tables, on the columns that they JOIN on. If you already have indexes on these columns, perform an UPDATE STATISTICS on these tables/indexes. You may also want to try a DBCC DBREINDEX on these tables, your performance problems could be from data fragmentation from a lot of INSERT/DELETE activity, and DBCC DBREINDEX will help.These could take a lot of time to run (several hours) so you should only do it during a maintenance window; after your backups are performed, for example.
|
|
|
|
Lou
Yak Posting Veteran
59 Posts |
Posted - 2002-02-21 : 16:32:30
|
| [quote]I am trying to get all data from TABLE A which is not in TABLE B.[quote]select a.*from a left outer join bon a.commonkey=b.commonkeywhere b.commonKey is null |
|
|
|
|
OUTER JOIN