Exclude holidays in Oracle table

Question

table1 K

Date          id
2015-01-01    10
2015-01-02    10
2015-01-03    10
2015-01-04    10
2015-01-05    10
2015-01-06    10

table2 H

Date          id   Holiday
2015-01-03    10   a holiday day 

I want to exclude holidays from my Date column and create a new column like AS new_dates. So the output is like:

table3 Output

new_dates     id
2015-01-01    10
2015-01-02    10
2015-01-04    10
2015-01-05    10
2015-01-06    10

Answer 1

Now edited. I was suggesting EXCEPT ALL, but Oracle has MINUS instead, according to MTO's answer.

select date, id from table1
MINUS
select date, id from table2

Or NOT EXISTS:

select date, id from table1 t1
where not exists (select 1 from table2 t2
                  where t1.date = t2.date
                    and t1.id = t2.id)

Edited: added and t1.id = t2.id to the NOT EXISTS version's sub-select.

Answer 2

Another way,

SQL> SELECT t1.* FROM t1, t2 WHERE t1."DATE" <> t2."DATE" AND t1.ID = t2.ID;

DATE              ID
--------- ----------
01-JAN-15         10
02-JAN-15         10
04-JAN-15         10
05-JAN-15         10
06-JAN-15         10

There could be different holidays for different IDs, so the condition checks for unmatched rows and filters them out and makes sure it is checked for a particular ID and not random.

From a performance point of view, with proper primary and foreign key constraints, you could do a single table scan on the referencing table rather than 2 table scans.

For example,

Adding the primary key and foreign key constraints:

SQL> ALTER TABLE t1
  2  ADD CONSTRAINT t1_pk PRIMARY KEY ("DATE", id);

Table altered.

SQL>
SQL> ALTER TABLE t2
  2  ADD CONSTRAINT t2_fk
  3     FOREIGN KEY ("DATE", ID)
  4     REFERENCES t1 ("DATE", id);

Table altered.

Let's check the explain plan:

SQL> set autot on explain
SQL> SELECT t1.* FROM t1, t2 WHERE t1."DATE" <> t2."DATE" AND t1.ID = t2.ID;

DATE              ID
--------- ----------
01-JAN-15         10
02-JAN-15         10
04-JAN-15         10
05-JAN-15         10
06-JAN-15         10


Execution Plan
----------------------------------------------------------
Plan hash value: 603358195

----------------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |     5 |   110 |     4   (0)| 00:00:01 |
|   1 |  NESTED LOOPS      |       |     5 |   110 |     4   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T2    |     1 |    11 |     3   (0)| 00:00:01 |
|*  3 |   INDEX FULL SCAN  | T1_PK |     5 |    55 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("T1"."ID"="T2"."ID")
       filter("T1"."DATE"<>"T2"."DATE" AND "T1"."ID"="T2"."ID")

Note
-----
   - this is an adaptive plan

So, there is a full table scan only on T2 and an INDEX FULL SCAN on the primary key index of T1.

Now, there should be a supporting index for the foreign key, which will avoid the full table scan and use the index scan.

Let's create the supporting index for the foreign key:

SQL> CREATE INDEX t2_indx ON t2("DATE", id);

Index created.

Let's check the explain plan:

SQL> set autot on explain
SQL> SELECT t1.* FROM t1, t2 WHERE t1."DATE" <> t2."DATE" AND t1.ID = t2.ID;

DATE              ID
--------- ----------
01-JAN-15         10
02-JAN-15         10
04-JAN-15         10
05-JAN-15         10
06-JAN-15         10


Execution Plan
----------------------------------------------------------
Plan hash value: 2278991468

----------------------------------------------------------------------------
| Id  | Operation        | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |         |     5 |   110 |     2   (0)| 00:00:01 |
|   1 |  NESTED LOOPS    |         |     5 |   110 |     2   (0)| 00:00:01 |
|   2 |   INDEX FULL SCAN| T2_INDX |     1 |    11 |     1   (0)| 00:00:01 |
|*  3 |   INDEX FULL SCAN| T1_PK   |     5 |    55 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("T1"."ID"="T2"."ID")
       filter("T1"."DATE"<>"T2"."DATE" AND "T1"."ID"="T2"."ID")

Note
-----
   - this is an adaptive plan

Now, there are no full table scans, rather optimizer uses INDEX FULL SCAN.

Answer 3

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE T1 ( "DATE", ID ) AS
SELECT DATE '2015-01-01' + LEVEL - 1, 10
FROM   DUAL
CONNECT BY LEVEL < 7;

CREATE TABLE T2 ( "DATE", ID ) AS
SELECT DATE '2015-01-03', 10 FROM DUAL;

Query 1:

SELECT * FROM T1
MINUS
SELECT * FROM T2

Results:

|                      DATE | ID |
|---------------------------|----|
| January, 01 2015 00:00:00 | 10 |
| January, 02 2015 00:00:00 | 10 |
| January, 04 2015 00:00:00 | 10 |
| January, 05 2015 00:00:00 | 10 |
| January, 06 2015 00:00:00 | 10 |

Query 2:

SELECT * FROM T1
WHERE NOT EXISTS ( SELECT 'X'
                   FROM   T2
                   WHERE  T1.ID = T2.ID
                   AND    T1."DATE" = T2."DATE" )

Results:

|                      DATE | ID |
|---------------------------|----|
| January, 01 2015 00:00:00 | 10 |
| January, 05 2015 00:00:00 | 10 |
| January, 04 2015 00:00:00 | 10 |
| January, 06 2015 00:00:00 | 10 |
| January, 02 2015 00:00:00 | 10 |

Query 3:

This query is valid in SQL Server but not in Oracle:

SELECT * FROM T1
EXCEPT ALL
SELECT * FROM T2

Results:

ORA-00933: SQL command not properly ended 

Answer 4

Simple

select date, id from table1 
where not exists(select 'x' from table2 where table1.date = table2.date)