Stack Overflow
Stack Overflow

Questions tagged [notin]

The NOT IN operator acts opposite to "IN" Operator. It can be used to specify multiple values that should NOT be present in a WHERE clause. Note that NOT IN never is true!

NOT Logical Operator

If you want to find rows that do not satisfy a condition, you can use the logical operator, NOT. NOT results in the reverse of a condition. That is, if a condition is satisfied, then the row is not returned.

NOT IN can be represented as conjunction of NOT equal comparison operators.

A NOT IN (B, C) <=> (NOT A=B) AND (NOT A=C) If one of A or B is NULL then NOT IN operator is not True (Undefined)

MSSQL NOT

IN Comparison Operator

The IN operator is used when you want to compare a column with more than one value. It is similar to an OR condition.

MSSQL IN Clause

277 questions
3
votes
3 answers

Need help optimizing MySQL query with "not in" join

My query is currently taking roughly 3 seconds, which I'm sure can be optimized. I just can't figure out how to optimize it. My app has a reasonably big products table (roughly 500,000 records). Each product can be listed on one of 50 domains…
rickdenhaan
  • 8,637
  • 20
  • 30
3
votes
4 answers

NOT IN query... odd results

I need a list of users in one database that are not listed as the new_user_id in another. There are 112,815 matching users in both databases; user_id is the key in all queries tables. Query #1 works, and gives me 111,327 users who are NOT…
James King
  • 6,000
  • 5
  • 37
  • 61
2
votes
2 answers

How to use NOT IN in an SQL subquery?

I am trying to find the drinker and the drink liked by the drinker but the drinker hasn't ordered the drink so far. I also need to sort the drinker and the drink pairs in the ascending order of drinkers and drinks. Note: I can only use IN or NOT IN…
Noods
  • 433
  • 1
  • 9
2
votes
1 answer

Alternative to Hive Query with NOT IN clause

I have the following set of hive tables : create table image_additions ( customer_id STRING, image_key STRING, image_size STRING ); create table image_removals ( customer_id STRING, image_key STRING, image_size STRING ); create…
dlsa
  • 2,884
  • 2
  • 15
  • 15
2
votes
2 answers

How to return keys which are not in a list?

I have the following dict: d = {'Z':1,'A':2,'C':3} I want to return all the keys that are not in ['A','B'] I know that [k for k in d.keys() if 'A' not in k] works but [k for k in d.keys() if ['A','B'] not in k] doesn't because not in expects a…
RealRageDontQuit
  • 337
  • 2
  • 11
2
votes
2 answers

MSQL How can I use a many statement in NOT IN

I try to execute this query: SELECT ISBN FROM table2 WHERE NOT IN ISBN=('8426429807','840149768X') group by ISBN ORDER BY AVG(`Book-Rating`) DESC LIMIT 10 but I get error this shape 1064 - You have an error in your SQL syntax; check the manual…
Alper C
  • 25
  • 3
2
votes
1 answer

Not in clause not filtering SQL Server with open query

So I have two tables that look like this Inventory table (this is just pulling a list of empty locations) |loc| |ECA001| _____ Inventory Transfer Job List table |id | oid | sku | from_loc | to_loc | tag | qty | processed | create_date |…
Charles Cooper
  • 49
  • 5
2
votes
5 answers

SQL Server: NOT IN in where clause not working with NVARCHAR

I have below table: DECLARE @P AS TABLE ( ID int, PID int, PNAME NVARCHAR(30), PARENT_PNAME NVARCHAR(30), Error int ) INSERT INTO @P…
Ralph
  • 7,746
  • 14
  • 84
  • 190
2
votes
2 answers

How can I improve query performance

I have a piece of query that spinning forever. I am new in DBA stuff. I would like to know what would be the first thing to deal with such problems? I looked at the Execution Plan The table has only 1 non clustered index on ClassCode. The reason I…
Serdia
  • 3,649
  • 11
  • 53
  • 111
2
votes
3 answers

NOT IN and a SQL join

I've written a SQL statement where these specific columns are from a table and it joins with another table and the primary key, ReportID, serves as the link between the two tables. I am using NOT IN to accurately display the reports of a company,…
Ron Ronmonstererer Ankrah
  • 71
  • 10
2
votes
2 answers

SQL WHERE NOT...IN vs WHERE ... NOT IN

Is there any difference or best practices between the following two statements, syntax, performance, style, or otherwise? SELECT TitleOfCourtesy, FirstName, LastName FROM Employees WHERE NOT TitleOfCourtesy IN ('Ms.','Mrs.'); SELECT…
chrispbacon
  • 33
  • 3
2
votes
1 answer

Filtering a MySQL query with multiple conditions

I have my output array named "Feed Items" (fi): ( [0] => Array ( [reg] => 2015-08-03 13:39:00 [id] => fd7ec4107d16b07c1a13cbdd386af8d2cb05ffca [user_id] => de5fd44db1760b006b1909cf1db11a78b38e455c …
cwiggo
  • 2,345
  • 8
  • 38
  • 82
2
votes
3 answers

NOT IN Operator not working as expected in Oracle

I have two tables: PROD and CUST On running the below query in SQL and SYBASE, it works. Oracle is not giving any results. select * FROM PROD where PROD.SECID NOT IN (SELECT CUST.SECID FROM CUST WHERE SECID <> '') NOTE: PROD.SECID has all null…
user3346282
  • 33
  • 1
  • 10
2
votes
1 answer

Wordpress get posts not in custom taxonomy term

The following code is supposed to get posts that do not have specific terms in a custom taxonomy. At the moment it still gets them. Is something missing. $args = array( 'numberposts' => '3', 'post__not_in' => $post_not_in, …
RIK
  • 17,723
  • 34
  • 107
  • 185
2
votes
2 answers

Entity Framework query with "not in"

I have a simple (well easy, not simple) query of "not in" on related tables. SELECT CompetencyID, CompetencyName FROM Competency WHERE (Deleted = 0) AND (CompanyID = 1) AND (CompetencyID NOT IN(SELECT CompetencyID FROM CompetencyGroups WHERE…
ransems
  • 505
  • 6
  • 13
1 2
3
…
18 19