WHERE and WHERE NOT in SELECT

Question

How to use WHERE and WHERE NOT in the same SELECT SQL statement?

For example, I might have a table named fruit.

ID | Name       | Colour
-------------------------
1  | Strawberry | Red 
2  | Apple      | Red
3  | Grape      | Red
4  | Banana     | Yellow

From this table, I can execute SELECT * FROM [fruits] WHERE Colour = 'Red'

and it will get this:

ID | Name       | Colour
-------------------------
1  | Strawberry | Red 
2  | Apple      | Red
3  | Grape      | Red

However, I would like to exclude Apple

from one query above, I could use WHERE NOT

, but this will return all fruits including Banana

.

How would I write a SQL query so that it would result in the next, selected color, but excluding a specific fruit:

ID | Name       | Colour
-------------------------
1  | Strawberry | Red 
3  | Grape      | Red

I am using MsSQL 2014, any help would be appreciated.

+3

Sam bunting 03 Aug 17 at 14:17

5 answers

SELECT * 
FROM [fruits] 
WHERE 
Colour = 'Red'
and Name <> 'Apple'

+3

Schmocken 03 Aug 17 at 14:20

You can add several conditional expressions in the WHERE clause, simply by using keywords AND

and OR

.

For example:

SELECT * 
FROM [fruits] 
WHERE Colour = 'Red' AND NOT Name = 'Apple'

+2

Easton Bornemeier 03 Aug 17 at 14:20

select *
from [fruits]
where Colour = 'Red' and Name != 'Apple'

0

TheDetective 03 Aug 17 at 14:19

You can write the following query

SELECT * FROM [fruits] WHERE Colour = 'Red' and Name<>'Apple';

or

SELECT * FROM [fruits] WHERE Colour like 'Red' and Name not like 'Apple';

-1

Jignesh adesara 03 Aug 17 at 14:23

SqlZim · Accepted Answer · 2017-08-03T14:18:57+0000

As where

you may have a few suggestions:

select * 
from [fruits] 
where Colour = 'Red'
  and Name <> 'Apple'