{"id":1454,"date":"2012-07-09T05:12:33","date_gmt":"2012-07-09T10:12:33","guid":{"rendered":"http:\/\/www.dpriver.com\/blog\/?page_id=1454"},"modified":"2012-07-10T01:39:57","modified_gmt":"2012-07-10T06:39:57","slug":"oracle-sql-query-rewrite-not-in-sub-query-to-not-exists-sub-query","status":"publish","type":"page","link":"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/oracle-sql-query-rewrite\/oracle-sql-query-rewrite-not-in-sub-query-to-not-exists-sub-query\/","title":{"rendered":"Oracle SQL Query rewrite: (NOT) IN sub-query to (NOT) EXISTS sub-query"},"content":{"rendered":"

Change (NOT) IN sub-query to (NOT) EXISTS sub-query<\/p>\n

This is an article of Oracle SQL Query rewrite serials<\/a>.<\/p>\n

For this input sql:<\/p>\n

    \r\nSELECT *\r\nFROM   emp, salary\r\nWHERE  emp.emp_id = salary.emp_id\r\nAND    emp.dept_no IN (SELECT dept_no\r\n                       FROM   dept\r\n                       WHERE  dept.location = 'SEOUL'\r\n                       )\r\nAND    emp.emp_id NOT IN (SELECT emp_id\r\n                          FROM   emp_retirement c\r\n                          WHERE  c.retire_type = 'NORMAL'\r\n                          );\r\n<\/pre>\nSQL will be translated into:<\/p>\n\r\nSELECT *\r\nFROM   emp,\r\n       salary\r\nWHERE  emp.emp_id = salary.emp_id\r\n       AND EXISTS (SELECT 1\r\n                   FROM   dept\r\n                   WHERE  dept.location = 'SEOUL'\r\n                          AND emp.dept_no = dept.dept_no)\r\n       AND emp.emp_id NOT IN (SELECT emp_id\r\n                              FROM   emp_retirement c\r\n                              WHERE  c.retire_type = 'NORMAL') \r\n<\/pre>\nor <\/p>\n\r\nSELECT *\r\nFROM   emp,\r\n       salary\r\nWHERE  emp.emp_id = salary.emp_id\r\n       AND emp.dept_no IN (SELECT dept_no\r\n                           FROM   dept\r\n                           WHERE  dept.location = 'SEOUL')\r\n       AND NOT EXISTS (SELECT 1\r\n                       FROM   emp_retirement c\r\n                       WHERE  c.retire_type = 'NORMAL'\r\n                              AND emp.emp_id = c.emp_id) \r\n<\/pre>\nDownload this demo: C# version<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"Change (NOT) IN sub-query to (NOT) EXISTS sub-query This is an article of Oracle SQL Query rewrite serials. For this input sql: SELECT * FROM emp, salary WHERE emp.emp_id = salary.emp_id AND emp.dept_no IN (SELECT dept_no FROM dept WHERE dept.location = ‘SEOUL’ ) AND emp.emp_id NOT IN (SELECT emp_id FROM emp_retirement c WHERE c.retire_type = ‘NORMAL’ ); SQL will be translated into: SELECT * FROM emp, salary WHERE emp.emp_id = salary.emp_id AND EXISTS (SELECT 1 FROM dept WHERE dept.location = ‘SEOUL’ AND emp.dept_no = dept.dept_no) AND emp.emp_id NOT IN (SELECT emp_id FROM emp_retirement c WHERE c.retire_type = ‘NORMAL’) or SELECT\u2026<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":1445,"menu_order":0,"comment_status":"closed","ping_status":"open","template":"gsp_feature_page_tt.php","meta":[],"blocksy_meta":{"styles_descriptor":{"styles":{"desktop":"","tablet":"","mobile":""},"google_fonts":[],"version":5}},"yoast_head":"\nHelp you to make better use of General SQL Parser<\/title>\n\n\n\n\n\n\n\n\n\n\n\n\n\t\n