SQL Subqueries Oracle and ANSI Standard SQL

SQL SubqueriesOracle and ANSI Standard SQL Lecture 8

SQL SubqueriesJoin Types • Scalar subqueries • In-line views • Subqueries • Correlated Subqueries

SQL SubqueriesScalar Subquery: Defined • Returns only one thing: • A scalar variable, like a DATE, NUMBER, or STRING (VARCHAR2) data type. • A compound variable, like a user-defined SQL data type (implementation specific). • Returns only one row of data. • Works in the values clause of an INSERT statement. • Works in as the right operand of a SET clause in an UPDATE statement. • Works as an assignment operand in a WHERE clause in DELETE, SELECT, and UPDATE statements. • Also, known as a single row subquery.

SQL SubqueriesScalar Subquery: INSERT Statement • INSERT statements use scalar subqueries: INSERT INTO a_table a VALUES ('string_literal' , numeric_literal ,(SELECT b.unique_row_column FROM another_table b WHERE b.column_name ='super_key_value') ,'string_literal');

SQL SubqueriesScalar Subquery: UPDATE Statement • UPDATE statements use scalar subqueries: UPDATE a_table a SET a.column_1 = 'string_literal' , a.column_2 = numeric_literal , a.column_3 = (SELECT b.unique_row_column FROM another_table b WHERE b.column_name = 'super_key_value') , a.column_4 = 'string_literal' WHERE a.column_5 = 'string_literal';

SQL SubqueriesScalar Subquery: WHERE Clause • DELETE statement using WHERE clause: DELETE FROM a_table a WHERE a.column_1 = (SELECT b.unique_row_column FROM another_table b WHERE b.column_name = 'super_key_value') AND a.column_2 = 'string_literal';

SQL SubqueriesScalar Subquery: WHERE Clause • SELECT statement using WHERE clause: SELECT a.column_1 , a.column_2 FROM a_table a WHERE a.column_3 = 'string_literal' AND a.column_4 = numeric_literal AND a.column_5 = (SELECT b.unique_row_column FROM another_table b WHERE b.column_name = 'super_key_value') AND a.column_6 = 'string_literal';

SQL SubqueriesScalar Subquery: WHERE Clause • UPDATE statement using WHERE clause: UPDATE a_table a SET a.column_1 = 'string_literal' , a.column_2 = numeric_literal WHERE a.column_3 = (SELECT b.unique_row_column FROM another_table b WHERE b.column_name = 'super_key_value') AND a.column_4 = 'string_literal';

SQL SubqueriesIn-line View: Defined • Runs before the containing query and returns one or one set of column values: • The SELECT statement can return one to many columns of data in zero to many rows. • Works only in the FROM clause of a SELECT statement. • Works by mapping a subquery to a runtime alias, which acts like a table or view name in the containing query. • Works by enclosing a SELECT statement in parentheses. • Produces a Cartesian product or CROSS JOIN when not joined to another table, view or in-line view. • Produces an equijoin or non-equijoin intersection, outer join, or anti-join following the same rules as tables or views.

SQL SubqueriesInline View: Example • SELECT statement using WHERE clause: SELECT a.column_1 , a.column_2 FROM a_table a , (SELECT b.column_a , b.column_b FROM another_table b) iv WHERE a.column_4 = iv.column_b AND a.column_5 = 'string_literal';

SQL SubqueriesSubquery: Defined • Runs before the containing query and returns one or one set of column values: • The SELECTstatement can return one to many columns of data in zero to many rows. • Works as an assignment operand in a WHERE clause in DELETE, INSERT, SELECT, and UPDATE statements. • Cannot compare null values from the subquery. • Works with equality and inequality operators: • Single row: • Works with only the equality and inequality operators. • Multiple rows: • Works with the equality and inequality operators with the ALL, ANY or SOME operators. • Works with the [NOT] IN operator.

SQL SubqueriesSubquery: Equality/Inequality Example • SELECT statement using a single-row subquery returning a single column: SELECT a.column_1 , a.column_2 FROM a_table a WHERE a.column_3 = (SELECT b.column_a FROM another_table b WHERE b.column_c = 'super_key_value') AND a.column_4 = 'string_literal';

SQL SubqueriesSubquery: Equality/Inequality Example • SELECT statement using a single-row subquery returning multiple columns: SELECT a.column_1 , a.column_2 FROM a_table a WHERE (a.column_3, a.column_4) = (SELECT b.column_a , b.column_b FROM another_table b) AND a.column_5 = 'string_literal';

SQL SubqueriesSubquery: ALL, ANY, SOME Example • SELECT statement using a multiple row subquery returning a single column: SELECT a.column_1 , a.column_2 FROM a_table a WHERE a.column_3 = ANY (SELECT b.column_a FROM another_table b WHERE b.column_c = 'super_key_value') AND a.column_4 = 'string_literal';

SQL SubqueriesSubquery: ALL, ANY, SOME Example • SELECT statement using a multiple row subquery returning multiple columns: SELECT a.column_1 , a.column_2 FROM a_table a WHERE (a.column_3, a.column_4) = ANY (SELECT b.column_a , b.column_b FROM another_table b) AND a.column_5 = 'string_literal';

SQL SubqueriesSubquery: IN Example • SELECT statement using a multiple row subquery returning a single column: SELECT a.column_1 , a.column_2 FROM a_table a WHERE a.column_3 IN ('value1','value2','value3') AND a.column_4 = 'string_literal';

SQL SubqueriesSubquery: IN Example • SELECT statement using a multiple row subquery returning multiple columns: SELECT a.column_1 , a.column_2 FROM a_table a WHERE (a.column_3,a.column_4) IN ((number_1,'string_1'),(number_2,'string_2')) AND a.column_5 = 'string_1';

SQL SubqueriesSubquery: IN Example • SELECT statement using a multiple row subquery returning a single column: SELECT a.column_1 , a.column_2 FROM a_table a WHERE a.column_3 IN (SELECT b.column_a FROM another_table b WHERE b.column_c = 'super_key_value') AND a.column_4 = 'string_literal';

SQL SubqueriesSubquery: IN Example • SELECT statement using a multiple row subquery returning multiple columns: SELECT a.column_1 , a.column_2 FROM a_table a WHERE (a.column_3, a.column_4) IN (SELECT b.column_a , b.column_b FROM another_table b) AND a.column_5 = 'string_literal';

SQL SubqueriesCorrelated Subquery: Defined • Runs for each row in the containing query and returns one or one set of column values: • The SELECTstatement can return one to many columns of data in zero to many rows, which are resolved by a join between the subquery and containing query. • Works as an assignment operand in a WHERE clause in DELETE, SELECT, and UPDATE statements. • Can compare null values from the subquery. • Works with equality and inequality operators: • Single row: • Works with only the equality and inequality operators. • Multiple rows: • Works with the equality and inequality operators because of the subquery to containing query join opertions. • Works with the [NOT] EXISTS operator.

SQL SubqueriesCorrelated Subquery: Example • SELECT statement using a zero to many row subquery returning a row to rows of data: SELECT a.column_1 , a.column_2 FROM a_table a WHERE EXISTS (SELECT NULL FROM another_table b WHERE b.column_c = a.column_4) AND a.column_3 = 'string_literal';

SQL SubqueriesCorrelated Subquery: Example • UPDATE statement using a zero to many row subquery returning a row to rows of data: UPDATE a_table a SET a.column_1 = 'string_literal' , a.column_2 = numeric_literal WHERE EXISTS (SELECT NULL FROM another_table b WHERE b.column_name = a.column_4) AND column_3 = 'string_literal'; • Correlated UPDATE statements change rows that meet the join condition in the subquery.

SQL SubqueriesCorrelated Subquery: Example • DELETE statement using a zero to many row subquery returning a row to rows of data: DELETE FROM a_table a WHERE EXISTS (SELECT b.unique_row_column FROM another_table b WHERE b.column_name = a.column_2) AND a.column_2 = 'string_literal'; • Correlated DELETE statements change rows that meet the join condition in the subquery.

Summary • Scalar subqueries • In-line views • Subqueries • Correlated Subqueries

SQL Subqueries Oracle and ANSI Standard SQL