How to write this statment by using join without using (not in) statment ?

Question

0.00/5 (No votes)

See more:

How to write this statement by using join without using (not in) statement ?

I work on SQL server 2012

I need to use alternative query statement to (not in)

using join and where condition only

Are this possible or not ?

What I have tried:

SELECT PartID, SupplierId
FROM #parts 
WHERE PartID NOT IN (
	SELECT t.PartID
	FROM #tempsupplier AS s
	INNER JOIN #TradeCodes AS t ON t.TradeCode = s.TradeCode
);

Details if you need

create table #tempsupplier
(
SupplierId  int,
TradeCode  int
)
insert into #tempsupplier(SupplierId,TradeCode) 
values 
(10,15)

create table #parts
(
PartID  int,
PartNumber  nvarchar(200),
SupplierId int,
TradeCode int
)

insert into #parts
(PartID,PartNumber,SupplierId) 
values
(100,'silicon',10),
(200,'motherboard',10),
(300,'iron',10),
(400,'plastic',10),



create table #TradeCodes
(
PartID int,
TradeCode int
)
insert into #TradeCodes
(PartID,TradeCode) 
values
(300,15),
(400,15)

FinalResult

partid  companyid  

100       10
200       10

Posted 24-Jan-20 0:35am

ahmed_sa

Updated 24-Jan-20 21:13pm

Add a Solution

Comments

MadMyche 24-Jan-20 7:12am

What is wrong with using NOT IN?

dnibbo 24-Jan-20 7:37am

I don't think there is anything wrong with what you have if I am reading this correctly. NOT IN is perfectly OK.
Are you trying to find all 'parts' that have no Tradecode?

You could use

SELECT p.PartID, p.SupplierID
    FROM #parts p
    LEFT OUTER JOIN #TradeCodes t ON t.PartID = p.PartID
    WHERE t.TradeCode IS NULL

ahmed_sa 24-Jan-20 7:41am

thank you for reply
statement i write (not in)
give me correct result
but I need using join with where if there are condition
are this possible or not

2 solutions

Solution 3

Event though this is already marked as answered just to give another alternative to the NOT IN structure.

While NOT IN is often a feasible choice it can lead to performance problems or even run time errors if the in list is big. To avoid this one way is to convert the NOT IN condition to a correlated NOT EXISTS condition.

Consider the following example

SQL

SELECT PartID, SupplierId
FROM  #parts p
WHERE NOT EXISTS (SELECT 1
                  FROM #tempsupplier  s
                  INNER JOIN #TradeCodes t ON t.TradeCode = s.TradeCode
                  WHERE t.PartID = p.PartID
);

Posted 24-Jan-20 21:14pm

Wendelius

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

MadMyche · Accepted Answer · 2020-01-24T01:41:00

There are a couple of ways to achieve this.

Here is your original query, along with two other versions based on the query you provided (based on what I authored a day ago).

SQL

SELECT PartID, SupplierId
FROM #parts 
WHERE PartID NOT IN (
	SELECT t.PartID
	FROM #tempsupplier AS s
	INNER JOIN #TradeCodes AS t ON t.TradeCode = s.TradeCode
);

SELECT p.PartID, p.SupplierId
FROM   #parts                 p
LEFT OUTER JOIN #TradeCodes   t ON p.PartID = t.PartID
LEFT OUTER JOIN #tempsupplier s ON t.TradeCode = s.TradeCode
WHERE t.PartID is  NULL

SELECT p.PartID, p.SupplierId
FROM   #parts      p
LEFT OUTER JOIN (
     SELECT t.PartID
     FROM   #tempsupplier   AS s
     INNER JOIN #TradeCodes AS t ON t.TradeCode = s.TradeCode
) x ON p.PartID = x.PartID
WHERE x.PartID is  NULL

I will reiterate that you need to use the tools properly (SSMS => Show Actual Execution Plan) to see which one is the most efficient. The first 2 queries are equal in performance; while the JOIN to the subquery is much less efficient.