8i | 9i | 10g | 11g | 12c | 13c | 18c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » Misc » Here

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.

Related articles.

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.

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:

Hope this helps. Regards Tim...

Back to the Top.