Join Queries  «Prev 

Change FROM IN to EXISTS

  1. This simulation lets you practice converting a query FROM using the IN clause to using the EXISTS clause. Here is the query you will convert. It has been executed so you can see the results. To begin the conversion, you will edit the query using a text editor. Start up the text editor by typing EDIT next to the SQL> prompt and pressing Enter.
  2. SQL*Plus opens up a text editor and places the text of the query into the file so you can conveniently edit the query. Here you see the Microsoft Notepad is our editor. (You can define a different default editor if you prefer.) Now, begin converting this query by adding an alias for the table in the FROM clause. Type a space and the letter C immediately after the name of the table in the FROM clause.
  3. The next step is to revise the WHERE clause. Replace "CUST_ID IN" with "EXISTS" on the third line of the query text.
  4. Now you revise the SELECT clause in the sub-query. Go to the fourth line of the query and replace "CUST_ID" with the letter X in single quotes.
  5. The next step is to add an alias on the table in the sub-query. Go to the fifth line of the query and add a blank space and the capital letter D.
  6. The next step is to add another comparison, the one that relates the sub-query to the outer query. Open a new line for entering this information by placing your cursor just to the left of the closing parenthesis and pressing Enter.
  7. The next step is to add another comparison -- the one that relates the sub-query to the outer query. Open a new line for entering this information by placing your cursor just to the left of the closing parenthesis and pressing Enter.
  8. The query is complete! Now close the text editor. Click the X in the top right corner of the window.
  9. Before closing the file, Notepad asks you to save your changes with this warning box. Click the Yes button to save your changes.
  10. You are back in SQL*Plus with the modified query in your buffer and ready to execute. To execute the new query, type a forward slash and press Enter.
  11. SQL*Plus executes the query and displays the results. As you can see, the results are the same as the original query. Click OK to exit the simulation.