EXISTS Operator

Where exists (subquery)

The WHERE EXISTS subquery is used when we want to display all rows where we have a matching column in both tables. In most cases, this type of subquery can be re-written with a standard join to improve performance.

Where not exists (subquery)

The WHERE NOT EXISTS subquery is used to display cases where a selected column does not appear in another table.

As a general rule, the use of the NOT EXISTS subqueries are discouraged because the query can often be re-written as a standard join with much faster performance.

Some related examples.

1. Use EXISTS to link two queries.

SQL> SELECT * FROM emp WHERE EXISTS
  2  (select * from dept where dept.deptno = emp.deptno);

     EMPNO ENAME      JOB              MGR HIREDATE         SAL
---------- ---------- --------- ---------- --------- ---------- 
      7934 MILLER     CLERK           7782 23-JAN-82       1300 
      7839 KING       PRESIDENT            17-NOV-81       5000 
      7782 CLARK      MANAGER         7839 09-JUN-81       2450 
      7902 FORD       ANALYST         7566 03-DEC-81       3000 
      7876 ADAMS      CLERK           7788 12-JAN-83       1100 
      7788 SCOTT      ANALYST         7566 09-DEC-82       3000 
      7566 JONES      MANAGER         7839 02-APR-81       2975 
      7369 SMITH      CLERK           7902 17-DEC-80        800 
      7900 JAMES      CLERK           7698 03-DEC-81        950 
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500 
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850 

     EMPNO ENAME      JOB              MGR HIREDATE         SAL 
---------- ---------- --------- ---------- --------- ---------- 
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250 
      7521 WARD       SALESMAN        7698 22-FEB-81       1250 
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600 

2. The EXISTS condition can also be combined with the NOT operator.

SQL>     SELECT *
  2      FROM emp
  3      WHERE not exists (select * from dept 
  4                        Where dept.deptno = emp.deptno);

3. Test the NOT EXISTS version.

SQL> SELECT cust_no, lastname
  2    FROM customer c
  3    WHERE NOT EXISTS
  4    (SELECT cust_no FROM ord
  5     WHERE cust_no = c.cust_no);
   CUST_NO LASTNAME
---------- --------------------
         5 Hill
         8 Chili
         3 Jason
        10 Hack
        14 Pete
        13 Richer
        11 Bill

7 rows selected.

4. Display the customer number and last name of all customers with no orders.

SQL> select cust_no, lastname
  2    from customer c
  3    where not exists
  4    (select * from ord o where o.cust_no = c.cust_no);

   CUST_NO LASTNAME
---------- --------------------
         5 Hill
         8 Chili
         3 Jason
        10 Hack
        14 Pete
        13 Richer
        11 Bill

7 rows selected.

GROUP BY Clause

Whenever you do a group by or order by operation, Oracle will invoke a sorting operation. Using the group by inside SQL syntax is also useful for doing summaries, rollups and cubic operations.

Grouping is a special type of sorting. With sorting, all the rows are sorted by the columns specified. Grouping sorts the rows into groups so that multi row functions can be specified at the group level.

Some related examples.

1. Use avg, sum, max and count functions with group.

SQL> select job_id, avg(salary), sum(salary), max(salary), count(*)
  2        from employees
  3       group by job_id
  4      /

JOB_ID             AVG(SALARY) SUM(SALARY) MAX(SALARY)   COUNT(*)
----------- ----------- ----------- ----------- ----------
PROG                 21000       84000       24000          4
DBA                  20000       40000       20000          2
MGR                  27500       55000       30000          2

3 rows selected.

2. Must include a nonaggregate column in the SELECT list in the GROUP BY clause.

SQL> SELECT City, MAX(Salary) AS "Highest Cost"
  2  FROM Employee
  3  GROUP BY City;

CITY       Highest Cost
---------- ------------
New York        7897.78
Toronto         1234.56
Vancouver       6661.78

3. Grouping at Multiple Levels: group by more than one column.

SQL> SELECT count(*), city, description
  2  FROM employee
  3  GROUP BY city, description;

  COUNT(*) CITY       DESCRIPTION
---------- ---------- ---------------
         1 New York   Manager
         4 Vancouver  Tester
         1 Toronto    Programmer
         1 Vancouver  Manager
         1 New York   Tester

4. Column sequence in the group by impacts the ordering.

SQL> SELECT count(*), city, description
  2  FROM employee
  3  GROUP BY description, city;

  COUNT(*) CITY       DESCRIPTION
---------- ---------- ---------------
         1 Toronto    Programmer
         4 Vancouver  Tester
         1 Vancouver  Manager
         1 New York   Manager
         1 New York   Tester

SQL>
SQL> SELECT count(*), city, description
  2  FROM employee
  3  GROUP BY  city, description;

  COUNT(*) CITY       DESCRIPTION
---------- ---------- ---------------
         1 New York   Manager
         4 Vancouver  Tester
         1 Toronto    Programmer
         1 Vancouver  Manager
         1 New York   Tester

5. Using the ORDER BY Clause to Sort Groups.

SQL> SELECT city, COUNT(city)
  2  FROM employee
  3  GROUP BY city
  4  ORDER BY COUNT(city);

CITY       COUNT(CITY)
---------- -----------
Toronto              1
New York             2
Vancouver            5

6. You don't have to include the columns used in the GROUP BY clause in your SELECT clause.

SQL> SELECT COUNT(description)
  2  FROM employee
  3  GROUP BY city
  4  ORDER BY COUNT(id);

COUNT(DESCRIPTION)
------------------
                 1
                 2
                 5

7. GROUP BY and HAVING clauses.

SQL> SELECT city, AVG(salary)
  2  FROM employee
  3  GROUP BY city
  4  HAVING AVG(salary) > 3000;

CITY       AVG(SALARY)
---------- -----------
New York       6110.28
Vancouver      3823.78

8. Using a Column Multiple Times in a GROUP BY Clause.

SQL> SELECT division_id, job_id, SUM(salary)
  2  FROM employee
  3  GROUP BY division_id, ROLLUP(division_id, job_id);

DIV JOB SUM(SALARY)
--- --- -----------
BUS PRE      800000
SAL MGR      350000
SAL WOR      490000
SUP MGR      200000
BUS          800000
SAL          840000
SUP          200000
BUS          800000
SAL          840000
SUP          200000

10 rows selected.

HAVING Clause

The Oracle HAVING clause is used in conjunction with the Oracle GROUP BY clause to limit the returned rows after the grouping.

SQL statements can utilize both a WHERE clause and an Oracle HAVING clause. The WHERE clause will filter rows as they are selected from the table, and before grouping, the Oracle HAVING clause will filter rows after the grouping.

Some related examples.

1. Example using the MAX function with having clause.

SQL> SELECT JOB, MAX(sal) as "Highest salary"
  2      FROM emp
  3      GROUP BY job
  4      HAVING MAX(sal) < 50000;

JOB       Highest salary
--------- --------------
CLERK               1300
SALESMAN            1600
PRESIDENT           5000
MANAGER             2975
ANALYST             3000

2. Using the HAVING Clause.

SQL> SELECT City, AVG(Salary) AS "Average Salary"
  2  FROM Employee
  3  GROUP BY City
  4  HAVING AVG(Salary) > 500;

CITY       Average Salary
---------- --------------
New York          6110.28
Toronto           1234.56
Vancouver         3823.78

3. Any conditions based on the outcome of a group function must be in the HAVING clause.

SQL> SELECT City, AVG(Salary) AS "Average Salary"
  2  FROM Employee
  3  WHERE City ='Vancouver'
  4  GROUP BY City
  5  HAVING AVG(Salary) > 1000;

CITY       Average Salary
---------- --------------
Vancouver         3823.78

4. Using the SUM function in HAVING Clause.

SQL> SELECT job, SUM(sal) as "Total salary"
  2      FROM emp
  3      GROUP BY job
  4      HAVING SUM(sal) > 1000;

JOB       Total salary
--------- ------------
CLERK             4150
SALESMAN          5600
PRESIDENT         5000
MANAGER           8275
ANALYST           6000

5. Using HAVING with an Analytical Function.

SQL> -- if a HAVING clause is added, 
SQL> -- it will have its effect just before the RANK.
SQL> SELECT j.jobtitle, COUNT(*), MAX(orig_salary) maxsalary,
  2    MIN(orig_salary) minsalary
  3    --   RANK() OVER(ORDER BY MAX(orig_salary)) rankorder
  4  FROM employee e, job j
  5  WHERE e.orig_salary < 43000
  6    AND e.empno = j.empno
  7  GROUP BY j.jobtitle
  8  HAVING MAX(orig_salary) > 34000
  9  ORDER BY j.jobtitle desc;

no rows selected

6. Sub query inside having clause.

SQL> SELECT count(*), city
  2  FROM employee
  3  GROUP BY city
  4  HAVING count(*) =
  5  (SELECT max(count(*))
  6  FROM employee
  7  GROUP BY city);

  COUNT(*) CITY
---------- ----------
         5 Vancouver

Oracle LIKE Operater

Oracle Like Condition

The LIKE condition allows you to use wildcards in the where clause of an SQL statement. This allows you to perform pattern matching. The LIKE condition can be used in any valid SQL statement - select, insert, update, or delete.

The patterns that you can choose from are.

% allows you to match any string of any length (including zero length).

_ allows you to match on a single character.

Some related examples.

1. Like with '__' (Any two characters).

SQL> select * from Employee
  2  WHERE First_Name LIKE'A____n';

ID   FIRST_NAME LAST_NAME  START_DAT END_DATE      SALARY CITY     
---- ---------- ---------- --------- --------- ---------- ---------
02   Alison     Mathews    21-MAR-76 21-FEB-86    6661.78 Vancouver 

2. Like with '_' and '%'.

SQL> SELECT First_Name
  2  FROM Employee
  3  WHERE First_Name LIKE'_e%';

FIRST_NAME
----------
Celia

3. Use two '%' in Like statement.

SQL> SELECT First_Name
  2  FROM Employee
  3  WHERE First_Name LIKE'%a%';

FIRST_NAME
----------
Jason
James
Celia
Linda
David
James

6 rows selected.

4. Using a NOT operator with like.

SQL> SELECT ID, First_Name, Last_Name
  2  FROM Employee
  3  WHERE City NOT LIKE'%Van%';

ID   FIRST_NAME LAST_NAME
---- ---------- ----------
01   Jason      Martin
06   Linda      Green
07   David      Larry

5. Use % in word ending.

SQL> SELECT * FROM product WHERE  product_name LIKE 'Chrome%';

no rows selected