How to get unique records without using DISTINCT keyword in oracle SQL?

Query to get jobs from EMP table

1. SELECT rownum,job

     FROM emp;

Output:

There are 14 records in EMP table.

    1.    Query to get unique records using DISTINCT keyword

1.             SELECT DISTINCT job

               FROM emp

              WHERE job IS NOT NULL;

  Output:

    

    2.    Query to get unique records using UNIQUE keyword

1.           SELECT UNIQUE job

             FROM emp

             WHERE job IS NOT NULL;

 Output:

 

    3.    Query to get unique records using GROUP BY clause

1.        SELECT   job, COUNT (*)

          FROM emp

          WHERE job IS NOT NULL

          GROUP BY job

          ORDER BY job;

Output:

Here we can see count(*) column having 13 records, another one employee does not have any jobs so that should be null.

    4.    Query to get unique records using UNION operator

1.          SELECT job

             FROM emp

            WHERE job IS NOT NULL

            UNION

            SELECT job

             FROM emp

            WHERE job IS NOT NULL;  

Output:

 

 Another example in Union operator:

1. SELECT job

     FROM emp

    WHERE job IS NOT NULL

   UNION

   SELECT NULL

     FROM DUAL

    WHERE 1 = 2;

Output:

 5.  Query to get unique records using INTERSECT operator

1. SELECT job

     FROM emp

    WHERE job IS NOT NULL

   INTERSECT

   SELECT job

     FROM emp

    WHERE job IS NOT NULL;

Output:

      6. Query to get unique records using MINUS operator

1. SELECT job

           FROM emp

          WHERE job IS NOT NULL

          MINUS

          SELECT NULL

          FROM emp;

Output:

    7.    Query to get unique records using SUBQUERY

1. SELECT job

     FROM emp a

    WHERE 1 = (SELECT COUNT (1)

                 FROM emp b

                WHERE a.job = b.job AND a.empno >= b.empno);

Output:

Share: