IT Tutorial IT Tutorial | Oracle DBA | SQL Server, Goldengate, Exadata, Big Data, Data ScienceTutorial
Hi,
I will explain IN and BETWEEN Operator in Oracle SQL in this post of Oracle SQL Tutorial series.
Read the previous post of this tutorial series before this.
COUNT(), AVG() and SUM() Functions in Oracle SQL | Oracle SQL Tutorials -14
IN Operator
You can use multiple values in a WHERE clause via IN Operator.
You can use IN Operator in a WHERE Clause instead of multiple OR conditions.
IN Syntax
IN () operator syntax is as follows.
SELECT * FROM table_name WHERE column_name IN (value1, value2, …);
SELECT column1,column2,.. FROM table_name WHERE column_name IN (value1, value2, ...);
IN operator examples are as follows.
SQL> select first_name,last_name,salary from hr.employees where manager_id IN (100,200,300); FIRST_NAME LAST_NAME SALARY -------------------- ------------------------- ---------- Neena Kochhar 23000 Lex De Haan 23000 Den Raphaely 12000 Matthew Weiss 9000 Adam Fripp 9200 Payam Kaufling 8900 Shanta Vollman 7500 Kevin Mourgos 6800 John Russell 15000 Karen Partners 14500 Alberto Errazuriz 13000 FIRST_NAME LAST_NAME SALARY -------------------- ------------------------- ---------- Gerald Cambrault 12000 Eleni Zlotkey 11500 Michael Hartstein 14000 14 rows selected. SQL> SQL> select first_name,last_name,salary from hr.employees where department_id in (20,30,40); FIRST_NAME LAST_NAME SALARY -------------------- ------------------------- ---------- Michael Hartstein 14000 Pat Fay 7000 Den Raphaely 12000 Alexander Khoo 4100 Shelli Baida 3900 Sigal Tobias 3800 Guy Himuro 3600 Karen Colmenares 3500 Susan Mavris 7500 9 rows selected. SQL>
SQL> select first_name,last_name,salary from hr.employees where department_id IN ( select department_id from hr.employees where salary IN (23000,30000,40000)); FIRST_NAME LAST_NAME SALARY -------------------- ------------------------- ---------- Neena Kochhar 23000 Lex De Haan 23000 SQL>
BETWEEN Operator
BETWEEN operator is used in the Where Clause and it is used to select values within a given range.
BETWEEN Syntax
BETWEEN syntax is as follows.
SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2;
BETWEEN Operator examples are as follows.
SQL> SQL> select first_name,last_name,salary from hr.employees where department_id BETWEEN 100 AND 500; FIRST_NAME LAST_NAME SALARY -------------------- ------------------------- ---------- Nancy Greenberg 13008 Daniel Faviet 10000 John Chen 9200 Ismael Sciarra 8700 Jose Manuel Urman 8800 Luis Popp 7900 Shelley Higgins 13008 William Gietz 9300 8 rows selected. SQL> SQL> SQL> select first_name,last_name,salary from hr.employees where salary between 15000 and 25000; FIRST_NAME LAST_NAME SALARY -------------------- ------------------------- ---------- Neena Kochhar 23000 Lex De Haan 23000 John Russell 15000 SQL>
Do you want to learn Oracle SQL Tutorial for Beginners, then read the following articles.
Oracle SQL Tutorials For Beginners – Learn Oracle SQL from scratch with Oracle SQL Online Course
