Hi,
I will explain AND, OR and NOT Operators in Oracle SQL in this post of Oracle SQL Tutorial series.
Read the previous post of this tutorial series before this.
SQL WHERE Clause and Comparison Operator in Oracle SQL | Oracle SQL Tutorials -4
AND, OR, and NOT operators are used in the Where clause in Oracle SQL. More than two or more Where condition can be combined with these operators.
AND Syntax
AND syntax is as follows. You can display only records that Both condition 1 AND condition 2 is TRUE or not.
SELECT column1, column2, ... FROM table_name
WHERE condition1 AND condition2;
List the deparments which are department id 30 AND location id 1700
SQL>
SQL> SELECT * FROM HR.DEPARTMENTS WHERE DEPARTMENT_ID=30 AND LOCATION_ID=1700;
DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
30 Purchasing 114 1700
SQL>
SQL>
List the deparments which are department id (30 or 40 or 50 ) AND manager id 203
SQL> SELECT * FROM HR.DEPARTMENTS WHERE DEPARTMENT_ID IN(30,40,50) AND MANAGER_ID=203;
DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
40 Human Resources 203 2400
SQL>
OR Syntax
OR syntax is as follows. You can display only records that EITHER condition 1 OR condition 2 is TRUE or not.
SELECT column1, column2, ... FROM table_name
WHERE condition1 OR condition2;
List the deparments which are department id less than 30 OR Salary greater than 10000.
SQL>
SQL> SELECT DEPARTMENT_ID, FIRST_NAME, LAST_NAME, SALARY FROM HR.EMPLOYEES WHERE DEPARTMENT_ID<30 OR SALARY>10000;
DEPARTMENT_ID FIRST_NAME LAST_NAME SALARY
------------- -------------------- ------------------------- ----------
90 Steven King 24000
90 Neena Kochhar 17000
90 Lex De Haan 17000
100 Nancy Greenberg 12008
30 Den Raphaely 11000
80 John Russell 14000
80 Karen Partners 13500
80 Alberto Errazuriz 12000
80 Gerald Cambrault 11000
80 Eleni Zlotkey 10500
80 Clara Vishney 10500
DEPARTMENT_ID FIRST_NAME LAST_NAME SALARY
------------- -------------------- ------------------------- ----------
80 Lisa Ozer 11500
80 Ellen Abel 11000
10 Jennifer Whalen 4400
20 Michael Hartstein 13000
20 Pat Fay 6000
110 Shelley Higgins 12008
17 rows selected.
List the Employees which are Salary less than 2500 OR Last name LIKE '%Kin%'
SQL> SELECT DEPARTMENT_ID, FIRST_NAME, LAST_NAME, SALARY FROM HR.EMPLOYEES WHERE SALARY<2500 OR LAST_NAME LIKE '%Kin%';
DEPARTMENT_ID FIRST_NAME LAST_NAME SALARY
------------- -------------------- ------------------------- ----------
90 Steven King 24000
50 James Landry 2400
50 Steven Markle 2200
50 TJ Olson 2100
50 Ki Gee 2400
50 Hazel Philtanker 2200
80 Janette King 10000
7 rows selected.
SQL>
NOT Syntax
NOT syntax is as follows. You can display only records that the conditions is NOT TRUE.
SELECT column1, column2, ... FROM table_name
WHERE NOT condition;
List the Employees which are NOT (department id 30 or Salary less than 10000) condition.
SQL> SELECT DEPARTMENT_ID, FIRST_NAME, LAST_NAME, SALARY FROM HR.EMPLOYEES WHERE NOT (DEPARTMENT_ID<30 OR SALARY<10000);
DEPARTMENT_ID FIRST_NAME LAST_NAME SALARY
------------- -------------------- ------------------------- ----------
30 Den Raphaely 11000
70 Hermann Baer 10000
80 John Russell 14000
80 Karen Partners 13500
80 Alberto Errazuriz 12000
80 Gerald Cambrault 11000
80 Eleni Zlotkey 10500
80 Peter Tucker 10000
80 Janette King 10000
80 Clara Vishney 10500
80 Lisa Ozer 11500
DEPARTMENT_ID FIRST_NAME LAST_NAME SALARY
------------- -------------------- ------------------------- ----------
80 Harrison Bloom 10000
80 Ellen Abel 11000
90 Steven King 24000
90 Neena Kochhar 17000
90 Lex De Haan 17000
100 Nancy Greenberg 12008
110 Shelley Higgins 12008
18 rows selected.
SQL>
List the Employees which are NOT (Salary less than 12000) condition. Namely, Query result will be Employee which Salary is greater than 12.000
SQL> SELECT DEPARTMENT_ID, FIRST_NAME, LAST_NAME, SALARY FROM HR.EMPLOYEES WHERE NOT SALARY<12000;
DEPARTMENT_ID FIRST_NAME LAST_NAME SALARY
------------- -------------------- ------------------------- ----------
90 Steven King 24000
90 Neena Kochhar 17000
90 Lex De Haan 17000
100 Nancy Greenberg 12008
80 John Russell 14000
80 Karen Partners 13500
80 Alberto Errazuriz 12000
20 Michael Hartstein 13000
110 Shelley Higgins 12008
9 rows selected.
SQL>
Do you want to learn Oracle Database for Beginners, then read the following articles.
Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA )