How To Use EXISTS In The Select Part of a Query in SQL

Have you ever had a situation where you would like to use an EXISTS in the SELECT part of your Oracle query..?
Iwork in the Data Warehouse team in a large MLM (Multi Level Marketing) company, and one of my responsibilities is to generate data for our many contests. Sometimes we would like to know i.e. which of the distributors that signed up from the beginning of the year have had any orders, and which of them have not.. Here is a trick that I have used sometimes.

Ok, this can of course be done in many ways, like having two queries and merge them with a union, join the customer table with the order table and use an outer join on the order table, etc. But, for the purpose of this little article I am going to use a trick that make me able to use an EXISTS in the SELECT part of my query.
Why would it be so important to use an EXISTS? Because, in our customer and order tables we are dealing with many millon records all together. So, I just want to have a list of the customers, and for each of the customers I just want to peak into the order table to check if the customer have had an order, or not. And, I do NOT want to i.e. do a COUNT of orders. Just a “get in, and get out” thing. The EXISTS “jumps out” as soon as it has found a record that fulfills the critera.
Ok, here we go. The trick is to use Oracle’s DUAL table.

1	SELECT    c.customer_id

2

,NVL((

3	SELECT    1

4	FROM    dual

5	WHERE    EXISTS (

6	SELECT    1

7	FROM    orders o

8	WHERE    o.commission_date >= TO_DATE('20100101', 'YYYYMMDD') - 7

9	AND    o.customer_id = c.customer_id

10

)

11	), 0) has_order

12	FROM    customer c

13	WHERE    c.application_date BETWEEN TO_DATE('20100101', 'YYYYMMDD') AND SYSDATE

1	-- Gives this resultset:

2	CUSTOMER_ID    HAS_ORDER

3	3488295        1

4	3488297        1

5	3488299        1

6	3488301        1

7	3488303        1

8	3488305        1

9	3488307        1

10	3488311        0

11	3488313        1

12	3488315        1

13	3488317        1

14	3488319        1

15	3488321        1

16	3488323        1

17	3488325        1

18	3488329        1

19	3488331        1

20	3488333        1

21	3488335        1

22	3488337        1

Hope this was helpful for you.