IN clause on multiple columns using AND , JOIN

Question

Which of the following queries are correct

SELECT
 ID, STATUS, ITEM_TYPE,CREATED_TIME,UPDATED_TIME
WHERE
 STATUS IN('OPEN','UPDATED')
AND
 ITEM_TYPE IN ('ITEM1','ITEM2')
AND
 CREATED_TIME BETWEEN 'XX' AND 'YY'
AND
 UPDATED_TIME BETWEEN 'XX' AND 'ZZ'

---

 SELECT
         ID, STATUS, ITEM_TYPE,CREATED_TIME,UPDATED_TIME
 WHERE
         STATUS IN('OPEN','UPDATED')
 JOIN

 SELECT
         ID, STATUS, ITEM_TYPE,CREATED_TIME,UPDATED_TIME
 WHERE
         ITEM_TYPE IN ('ITEM1','ITEM2')
 JOIN

 SELECT
         ID, STATUS, ITEM_TYPE,CREATED_TIME,UPDATED_TIME
 WHERE
         CREATED_TIME BETWEEN 'XX' AND 'YY'
 JOIN

 SELECT
         ID, STATUS, ITEM_TYPE,CREATED_TIME,UPDATED_TIME
 WHERE
         UPDATED_TIME BETWEEN 'XX' AND 'ZZ'

Answer 1

The first is correct, unless you want an exponential explosion of self-joins.

Answer 2

The first query is correct, the second would give, well....a LOT of reuslts.

In response to your comment, as SQLMenace pointed out, use OR instead if you want to match any of the conditions:

SELECT ID, STATUS, ITEM_TYPE,CREATED_TIME,UPDATED_TIME
WHERE STATUS IN('OPEN','UPDATED')
   OR ITEM_TYPE IN ('ITEM1','ITEM2')
   OR CREATED_TIME BETWEEN 'XX' AND 'YY'
   OR UPDATED_TIME BETWEEN 'XX' AND 'ZZ'

Or, if you wanted to always restrict on the time, but meet either of the first conditions, then use a wrapped OR:

SELECT ID, STATUS, ITEM_TYPE,CREATED_TIME,UPDATED_TIME
WHERE (STATUS IN('OPEN','UPDATED') OR ITEM_TYPE IN ('ITEM1','ITEM2'))
  AND CREATED_TIME BETWEEN 'XX' AND 'YY'
  AND UPDATED_TIME BETWEEN 'XX' AND 'ZZ'

Answer 3

the first one

JOINS have to be before the WHERE clause

although you could rewrite the JOINS with EXISTS and then you cut put it in the WHERE clause