This is the third part of a series of articles showing the basics of SQL. In this article we take a look at the type of things you are likely to see in the WHERE clause of queries.
You can perform all these queries online for free using SQL Fiddle.
The examples in this article require the following tables to be present.
--DROP TABLE employees PURGE; --DROP TABLE departments PURGE; CREATE TABLE departments ( department_id NUMBER(2) CONSTRAINT departments_pk PRIMARY KEY, department_name VARCHAR2(14), location VARCHAR2(13) ); INSERT INTO departments VALUES (10,'ACCOUNTING','NEW YORK'); INSERT INTO departments VALUES (20,'RESEARCH','DALLAS'); INSERT INTO departments VALUES (30,'SALES','CHICAGO'); INSERT INTO departments VALUES (40,'OPERATIONS','BOSTON'); COMMIT; CREATE TABLE employees ( employee_id NUMBER(4) CONSTRAINT employees_pk PRIMARY KEY, employee_name VARCHAR2(10), job VARCHAR2(9), manager_id NUMBER(4), hiredate DATE, salary NUMBER(7,2), commission NUMBER(7,2), department_id NUMBER(2) CONSTRAINT emp_department_id_fk REFERENCES departments(department_id) ); INSERT INTO employees VALUES (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20); INSERT INTO employees VALUES (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30); INSERT INTO employees VALUES (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30); INSERT INTO employees VALUES (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20); INSERT INTO employees VALUES (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30); INSERT INTO employees VALUES (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30); INSERT INTO employees VALUES (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10); INSERT INTO employees VALUES (7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87','dd-mm-rr')-85,3000,NULL,20); INSERT INTO employees VALUES (7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10); INSERT INTO employees VALUES (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30); INSERT INTO employees VALUES (7876,'ADAMS','CLERK',7788,to_date('13-JUL-87', 'dd-mm-rr')-51,1100,NULL,20); INSERT INTO employees VALUES (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30); INSERT INTO employees VALUES (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20); INSERT INTO employees VALUES (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10); COMMIT;
These tables are a variant of the EMP and DEPT tables from the SCOTT schema. You will see a lot of Oracle examples on the internet using the tables from the SCOTT schema. You can find the original table definitions in the "$ORACLE_HOME/rdbms/admin/utlsampl.sql" script.
The WHERE clause is used to filter the result set. If the filter conditions in the WHERE clause evaluate to FALSE for a specific row, that row is not returned in the final result set. Multiple filter conditions can be grouped together using AND and OR conditions.
When using the non-ANSI join syntax, the WHERE clause contains both join conditions and filter conditions.
SELECT e.employee_name, e.salary, d.department_id, d.department_name FROM employees e, departments d WHERE e.department_id = d.department_id -- Join Condition AND d.department_id = 20 -- Filter AND e.salary >= 2000 -- Filter ORDER BY e.employee_name; EMPLOYEE_N SALARY DEPARTMENT_ID DEPARTMENT_NAM ---------- ---------- ------------- -------------- FORD 3000 20 RESEARCH JONES 2975 20 RESEARCH SCOTT 3000 20 RESEARCH 3 rows selected. SQL>
When using the ANSI join syntax, the WHERE clause only contains filter conditions.
SELECT e.employee_name, e.salary, d.department_id, d.department_name FROM employees e JOIN departments d ON e.department_id = d.department_id WHERE d.department_id = 20 -- Filter AND e.salary >= 2000 -- Filter ORDER BY e.employee_name; EMPLOYEE_N SALARY DEPARTMENT_ID DEPARTMENT_NAM ---------- ---------- ------------- -------------- FORD 3000 20 RESEARCH JONES 2975 20 RESEARCH SCOTT 3000 20 RESEARCH 3 rows selected. SQL>
In the rest of this article we will take a look at the types of conditions you are likely to see in the WHERE clause. Remember, this is not an exhaustive list, just some of the most common things. To keep things simple, the following examples will use the ANSI join syntax, so the WHERE clause just contains filter conditions.
Filter conditions can use basic equality and inequality tests.
SELECT e.employee_name, e.department_id, e.salary FROM employees e WHERE e.department_id = 20 AND e.salary >= 2000 ORDER BY e.employee_name; EMPLOYEE_N DEPARTMENT_ID SALARY ---------- ------------- ---------- FORD 20 3000 JONES 20 2975 SCOTT 20 3000 3 rows selected. SQL>
The IN condition evaluates to TRUE for column values in the specified list.
SELECT e.department_id, e.employee_id, e.employee_name FROM employees e WHERE e.department_id IN (10, 20) ORDER BY e.department_id, e.employee_id; DEPARTMENT_ID EMPLOYEE_ID EMPLOYEE_N ------------- ----------- ---------- 10 7782 CLARK 10 7839 KING 10 7934 MILLER 20 7369 SMITH 20 7566 JONES 20 7788 SCOTT 20 7876 ADAMS 20 7902 FORD 8 rows selected. SQL>
The NOT IN condition evaluates to TRUE for column values not in the specified list.
SELECT e.department_id, e.employee_id, e.employee_name FROM employees e WHERE e.department_id NOT IN (10, 20) ORDER BY e.department_id, e.employee_id; DEPARTMENT_ID EMPLOYEE_ID EMPLOYEE_N ------------- ----------- ---------- 30 7499 ALLEN 30 7521 WARD 30 7654 MARTIN 30 7698 BLAKE 30 7844 TURNER 30 7900 JAMES 6 rows selected. SQL>
The IN and NOT IN conditions can be evaluated against values returned by a subquery.
SELECT e.department_id, e.employee_id, e.employee_name FROM employees e WHERE e.department_id IN (SELECT d.department_id FROM departments d WHERE d.department_id < 30)ORDER BY e.department_id, e.employee_id; DEPARTMENT_ID EMPLOYEE_ID EMPLOYEE_N ------------- ----------- ---------- 10 7782 CLARK 10 7839 KING 10 7934 MILLER 20 7369 SMITH 20 7566 JONES 20 7788 SCOTT 20 7876 ADAMS 20 7902 FORD 8 rows selected. SQL>
The EXISTS condition evaluates to TRUE if the subquery returns one or more rows.
SELECT d.department_id, d.department_name FROM departments d WHERE EXISTS (SELECT 1 FROM employees e WHERE d.department_id = e.department_id) ORDER BY d.department_id; DEPARTMENT_ID DEPARTMENT_NAM ------------- -------------- 10 ACCOUNTING 20 RESEARCH 30 SALES 3 rows selected. SQL>
The NOT EXISTS condition evaluates to TRUE if the subquery returns zero rows.
SELECT d.department_id, d.department_name FROM departments d WHERE NOT EXISTS (SELECT 1 FROM employees e WHERE d.department_id = e.department_id) ORDER BY d.department_id; DEPARTMENT_ID DEPARTMENT_NAM ------------- -------------- 40 OPERATIONS 1 row selected. SQL>
The BETWEEN condition evaluates to TRUE for column values within the specified range, boundaries inclusive.
SELECT d.department_id, d.department_name FROM departments d WHERE department_id BETWEEN 20 AND 40 ORDER BY d.department_id; DEPARTMENT_ID DEPARTMENT_NAM ------------- -------------- 20 RESEARCH 30 SALES 40 OPERATIONS 3 rows selected. SQL>
The NOT BETWEEN condition evaluates to TRUE for column values not within the specified range, boundaries inclusive.
SELECT d.department_id, d.department_name FROM departments d WHERE department_id NOT BETWEEN 20 AND 40 ORDER BY d.department_id; DEPARTMENT_ID DEPARTMENT_NAM ------------- -------------- 10 ACCOUNTING 1 row selected. SQL>
The LIKE condition evaluates to TRUE if there is a pattern match. The '%' is a variable length wildcard. The '_' is a single character wildcard.
SELECT d.department_id, d.department_name FROM departments d WHERE department_name LIKE '%O%' ORDER BY d.department_id; DEPARTMENT_ID DEPARTMENT_NAM ------------- -------------- 10 ACCOUNTING 40 OPERATIONS 2 rows selected. SQL>
The NOT LIKE condition evaluates to TRUE if there is no pattern match.
SELECT d.department_id, d.department_name FROM departments d WHERE department_name NOT LIKE '%O%' ORDER BY d.department_id; DEPARTMENT_ID DEPARTMENT_NAM ------------- -------------- 20 RESEARCH 30 SALES 2 rows selected. SQL>
Be careful when linking together conditions in the WHERE clause using the OR condition. It's easy to mess up your logic. In the following example I think I've asked for managers and clerks in department 20.
SELECT e.employee_id, e.employee_name, e.department_id, e.salary, e.job FROM employees e WHERE e.department_id = 20 AND e.job = 'MANAGER' OR e.job = 'CLERK' ORDER BY e.employee_id; EMPLOYEE_ID EMPLOYEE_N DEPARTMENT_ID SALARY JOB ----------- ---------- ------------- ---------- --------- 7369 SMITH 20 800 CLERK 7566 JONES 20 2975 MANAGER 7876 ADAMS 20 1100 CLERK 7900 JAMES 30 950 CLERK 7934 MILLER 10 1300 CLERK 5 rows selected. SQL>
We can see from the output we have returned employees from department 30 and 10 too. This is because the OR condition has cancelled out the department condition if the employee is a clerk.
We should have used parenthesis to make make sure we were asking the right question.
SELECT e.employee_id, e.employee_name, e.department_id, e.salary, e.job FROM employees e WHERE e.department_id = 20 AND (e.job = 'MANAGER' OR e.job = 'CLERK') ORDER BY e.employee_id; EMPLOYEE_ID EMPLOYEE_N DEPARTMENT_ID SALARY JOB ----------- ---------- ------------- ---------- --------- 7369 SMITH 20 800 CLERK 7566 JONES 20 2975 MANAGER 7876 ADAMS 20 1100 CLERK 3 rows selected. SQL>
We have just scratched the surface here. There many functions, conditions and operators you will come into contact with over the course of working with SQL, but they are all additions on top of this foundation. Understand the basics and the other stuff will fall into place over time.
Beginners often find joins rather confusing and tend to gravitate to the IN and EXISTS conditions to filter data, even in cases where it would be more efficient to join to the specific table and use a simple filter against it. In the early days you will probably focus on just trying to get the correct results, but for your long term development, you should try as many different methods to get the same results as possible and compare the performance. It's always good to know alternative solutions and over time you will get a feel for which conditions work best in which situations.
You also need to be aware that what you write is not always what Oracle runs. In some cases the optimizer it will transform (rewrite) your statement. For example, an IN condition using a list may be expanded to a group of equality conditions linked together with OR conditions. Don't get hung up on this in the early stages, but as you develop, you will need to start considering this when choosing the filter conditions to use.
For more information see:
Hope this helps. Regards Tim.
Created: 2015-09-07 Updated: 2019-03-23