sub queries group 2

8/3/2019 Sub Queries Group 2

1/21

Subquery

De Leon, Erick Bong

Jayco, Rayna Mae

Marquez, Juffrey

Paras, Jean Jill

Se, Reymond Tinao, Nodelyn Justine

GroupIII


2/21


3/21

What is a Subquery?

A subquery is a query in a query. It is also called an innerquery or a nested query. A subquery can be usedanywhere an expression is allowed. It is a query

expression enclosed in parentheses. Subqueries can beused with SELECT, INSERT, UPDATE, or DELETE

statements.

There is more than one way to execute an SQL task. Many

subqueries can be replaced by SQL joins. SQL joins areusually faster.


4/21

Tables to be Used in Examples


5/21



6/21



7/21


8/21

Subquery with the INSERT statement

We create a new Cars2 table with the samecolumns and datatypes as the Cars table. To find

out how a table was created, we can use theSHOW CREATE TABLE statement.

mysql> INSERT INTO Cars2 SELECT * FROMCars;


9/21

Subquery with the INSERT statement

This is a simplesubquery. We insert all

rows from the Carstable into the Cars2table.

The data was copied toa new Cars2 table.


10/21

Scalar subqueries

A scalar subquery returns a single value.


11/21

Scalar subqueries


12/21


13/21

Table subqueries

A table subquery returns a result table of zero or

more rows.


14/21

Table subqueries


15/21

Table subqueries

The previous query returns the names of thecustomers, who made some reservations. The

inner query returns customer Ids from theReservations table. We use the IN predicate to

select those names of customers, who have their

CustomerId returned from the inner selectquery.


16/21

Table subqueries

The previous subquery canbe rewritten using SQL join.


17/21

Correlated subqueries

A correlated subquery is a subquery thatuses values from the outer query in its

WHERE clause. The subquery is evaluatedonce for each row processed by the outer

query.


18/21


19/21

Subqueries with EXISTS, NOT

EXISTS

If a subquery returns any values, thenthe predicate EXISTS returns TRUE.

And NOT EXISTS FALSE.


20/21


21/21

Subqueries with EXISTS, NOT

EXISTS

In this query, we return all

customers that do not have anentry in the Reservations table.

Both SQL queries arecorrelated queries.

sub queries group 2

Documents