8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
SQL for Beginners (Part 3) : The WHERE Clause
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.
- Setup
- Introduction
- Equality and Inequality Conditions
- IN and NOT IN Conditions
- EXISTS and NOT EXISTS Conditions
- BETWEEN and NOT BETWEEN Conditions
- LIKE and NOT LIKE Conditions
- OR Condition
- Conclusion
Related articles.
- SQL for Beginners (Part 3) : The WHERE Clause
- SQL for Beginners - Full Playlist
- Oracle SQL Articles - Getting Started
Setup
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.
Introduction
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.
Equality and Inequality Conditions
Filter conditions can use basic equality and inequality tests.
- = : Equal.
- <> or != : Not equal.
- > : Greater than.
- < : Less than.
- >= : Great than or equal.
- <= : Less than or equal.
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>
IN and NOT IN Conditions
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>
EXISTS and NOT EXISTS Conditions
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>
BETWEEN and NOT BETWEEN Conditions
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>
LIKE and NOT LIKE Conditions
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>
OR Condition
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>
Conclusion
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:
- SQL for Beginners (Part 3) : The WHERE Clause
- SQL for Beginners - Full Playlist
- Oracle SQL Articles - Getting Started
- Database SQL Language Reference : SELECT
Hope this helps. Regards Tim...