SQL for Beginners (Part 3) : The WHERE Clause
In this article we take a look at the type of things you are likely to see in the WHERE clause of queries.
oracle miscconfigurationintermediate
by OracleDba
13 views
📋Steps in this guide1/9
1
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.
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.
Code/Command (click line numbers to comment):
123456789101112131415161718192021222324252627282930313233343536373839404142
--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;2
Introduction
The clause is used to filter the result set. If the filter conditions in the 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 conditions.
When using the non-ANSI join syntax, the clause contains both join conditions and filter conditions.
When using the ANSI join syntax, the clause only contains filter conditions.
In the rest of this article we will take a look at the types of conditions you are likely to see in the 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 clause just contains filter conditions.
Code/Command (click line numbers to comment):
1234567891011121314151617181920212223242526272829303132333435363738
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>
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>3
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.
Code/Command (click line numbers to comment):
1234567891011121314151617
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>4
IN and NOT IN Conditions
The condition evaluates to TRUE for column values in the specified list.
The condition evaluates to TRUE for column values not in the specified list.
The and conditions can be evaluated against values returned by a subquery.
Code/Command (click line numbers to comment):
1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162
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>
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>
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>5
EXISTS and NOT EXISTS Conditions
The condition evaluates to TRUE if the subquery returns one or more rows.
The condition evaluates to TRUE if the subquery returns zero rows.
Code/Command (click line numbers to comment):
123456789101112131415161718192021222324252627282930313233
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>
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>6
BETWEEN and NOT BETWEEN Conditions
The condition evaluates to TRUE for column values within the specified range, boundaries inclusive.
The condition evaluates to TRUE for column values not within the specified range, boundaries inclusive.
Code/Command (click line numbers to comment):
1234567891011121314151617181920212223242526272829
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>
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>7
LIKE and NOT LIKE Conditions
The condition evaluates to TRUE if there is a pattern match. The '%' is a variable length wildcard. The '_' is a single character wildcard.
The condition evaluates to TRUE if there is no pattern match.
Code/Command (click line numbers to comment):
1234567891011121314151617181920212223242526272829
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>
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>8
OR Condition
Be careful when linking together conditions in the clause using the 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.
We can see from the output we have returned employees from department 30 and 10 too. This is because the 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.
Code/Command (click line numbers to comment):
12345678910111213141516171819202122232425262728293031323334
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>
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>9
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 and 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 condition using a list may be expanded to a group of equality conditions linked together with 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...
Comments (0)
Please to add comments
No comments yet. Be the first to comment!