Not or : Logical operator « Query « SQL Server / T-SQL Tutorial






3> CREATE TABLE authors(
4>    au_id          varchar(11),
5>    au_lname       varchar(40)       NOT NULL,
6>    au_fname       varchar(20)       NOT NULL,
7>    phone          char(12)          NOT NULL DEFAULT ('UNKNOWN'),
8>    address        varchar(40)           NULL,
9>    city           varchar(20)           NULL,
10>    state          char(2)               NULL,
11>    zip            char(5)               NULL,
12>    contract       bit               NOT NULL
13> )
14> GO
1> insert authors values('1',  'Joe',   'Abra',   '111 111-1111', '6 St.', 'Berkeley',  'CA', '11111', 1)
2> insert authors values('2',  'Jack',  'Majo',   '222 222-2222', '3 St.', 'Oakland' ,  'CA', '22222', 1)
3> insert authors values('3',  'Pink',  'Cherry', '333 333-3333', '5 Ln.', 'Vancouver', 'BC', '33333', 1)
4> insert authors values('4',  'Blue',  'Albert', '444 444-4444', '7 Av.', 'Vancouver', 'BC', '44444', 1)
5> insert authors values('5',  'Red',   'Anne',   '555 555-5555', '6 Av.', 'Regina',    'SK', '55555', 1)
6> insert authors values('6',  'Black', 'Michel', '666 666-6666', '3 Pl.', 'Regina',    'SK', '66666', 1)
7> insert authors values('7',  'White', 'Sylvia', '777 777-7777', '1 Pl.', 'Rockville', 'MD', '77777', 1)
8> insert authors values('8',  'Yellow','Heather','888 888-8888', '3 Pu',  'Vacaville', 'CA', '88888', 0)
9> insert authors values('9',  'Gold',  'Dep',    '999 999-9999', '5 Av.', 'Oakland',   'CA', '99999', 0)
10> insert authors values('10', 'Siler', 'Dean',   '000 000-0000', '4 Av.', 'Oakland',   'CA', '00000', 1)
11> GO

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)
1>
2>
3>
4>
5> SELECT au_lname, au_fname, city, state FROM authors
6> WHERE
7> NOT
8> (
9> (city='OAKLAND' AND state='CA')
10> OR
11> (city='Salt Lake City' AND state='UT')
12> )
13> GO
au_lname                                 au_fname             city                 state
---------------------------------------- -------------------- -------------------- -----
Joe                                      Abra                 Berkeley             CA
Pink                                     Cherry               Vancouver            BC
Blue                                     Albert               Vancouver            BC
Red                                      Anne                 Regina               SK
Black                                    Michel               Regina               SK
White                                    Sylvia               Rockville            MD
Yellow                                   Heather              Vacaville            CA

(7 rows affected)
1>
2>
3> drop table authors;
4> GO
1>








1.26.Logical operator
1.26.1.The syntax of the WHERE clause with logical operators
1.26.2.A compound condition without parentheses
1.26.3.compound condition with parentheses
1.26.4.three Boolean operations have different priorities for evaluation:
1.26.5.SQL Server evaluates operators in this sequence: (1) NOT, (2) AND, and (3) OR.
1.26.6.Use OR to link two conditions
1.26.7.Use AND to link two conditions
1.26.8.AND, OR without parenthesis
1.26.9.parenthesis with AND, OR
1.26.10.And with parenthesis
1.26.11.Not or