what is the oposite of "on" in inner join? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

what is the oposite of "on" in inner join?

Author

Topic

asafg
Starting Member

39 Posts

Posted - 2008-12-31 : 04:19:29

I what to find the records that do not match between two tables:

table A | table B
id | id
1 | 2
2 | 4
3 |

1. a list of ids only in the right table... result (4)
2. a list of ids only in the left table... result (1, 3)
3. a list of ids in the left table... result (1, 3, 4)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-31 : 04:22:35

can we see what you tried till now?

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2008-12-31 : 04:23:07

select * from tabb where id not in (select id from taba)
select * from taba where id not in (select id from tabb)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-31 : 04:24:56

quote:
Originally posted by bklr

select * from tabb where id not in (select id from taba)

select * from tabb where id not in (select id from taba)taba a on a.id <> b.id

have you tested this. the second one is not even syntactically correct

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-31 : 04:26:44

This is correct for (1)
select * from tabb where id not in (select id from taba)

Why not simply change sources so that you also handle (2)
select * from taba where id not in (select id from tabb)

And (3)
select * from taba

E 12°55'05.63"
N 56°04'39.26"

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2008-12-31 : 04:26:51

quote:
Originally posted by visakh16

quote:
Originally posted by bklr

select * from tabb where id not in (select id from taba)

select * from tabb where id not in (select id from taba)taba a on a.id <> b.id

have you tested this. the second one is not even syntactically correct

just copy & paste mistake i have modified the the query

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-31 : 04:26:57

[code]
1.SELECT id
FROM tableB b
WHERE NOT EXISTS (SELECT 1 FROM tableA WHERE id = b.id)

2.SELECT id
FROM tableA a
WHERE NOT EXISTS (SELECT 1 FROM tableB WHERE id = a.id)

3.SELECT * FROM TableA
[/code]

suggest you to learn joins from books online. all the above are simple problems which you can get solution easily by understanding joins

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-31 : 04:38:41

[code]-- Prepare sample data
DECLARE @TableA TABLE
(
id INT
)

INSERT @TableA
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3

DECLARE @TableB TABLE
(
id INT
)

INSERT @TableB
SELECT 2 UNION ALL
SELECT 4

-- Solution for all questions at once
SELECT id,
CASE WHEN MIN(tbl) = 'b' THEN 'yes' ELSE 'no' END AS [Table B only],
CASE WHEN MAX(tbl) = 'a' THEN 'yes' ELSE 'no' END AS [Table A only],
CASE WHEN MIN(tbl) = 'a' THEN 'yes' ELSE 'no' END AS [Table A]
FROM (
SELECT id,
'a' AS tbl
FROM @TableA

UNION ALL

SELECT id,
'b'
FROM @TableB
) AS d
GROUP BY id
ORDER BY id[/code]

E 12°55'05.63"
N 56°04'39.26"

asafg
Starting Member

39 Posts

Posted - 2008-12-31 : 04:56:14

I have very big tables

this query that shows what is equal finish its execution in less then a second for 146k rows
select * from
(select IDNo from db.dbo.table1 where RunCode=2) as a
inner join
(select IDNo from db.dbo.table2 where RunCode=2) as b
on a.IDNo=b.IDNo

using the exist given by visakh16 does a great job but it takes over 3 seconds

Someone had told me in the past to make inner join manipulation like create a temp table and delete the inner join(the record that match) - and it works - It just look so twisted that I looked for another method with equal performance

visakh16 - thank you so much but it was a bit lazy to ask for 1 and two when they are the same but in three I was looking for a join of the results of 1 and two - which when I thing about it over was lazy two it can't be dome in one shot and requires a union.

Thanks
Asaf

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-31 : 05:01:45

welcome

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2008-12-31 : 06:30:21

select * from tabb a where not exists (select * from taba b where a.id = b.id)

select * from taba a where not exists (select * from tabb b where a.id = b.id)

select * from taba

Jai Krishna

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources