PLC Programming

What are the SQL Subquery and Exists Clause? (Part 6 of 8)

Learn about some of the advanced data definition SQL statements.

Listen to this article

In the previous article, What are the SQL INNER Join, And, OR, Having and Between Clause statement language elements, we learned about some of the more advanced SQL statements and clauses such as, AND, OR, BETWEEN, INNER JOIN and HAVING.

We also learned about the SQL syntax or rules of the SQL language.

In this article, you will learn about some of the advanced data definition SQL statements such as, Subquery and Exists.

Defining the Database

As we progress in learning SQL, we will learn to use statements for defining the database, statements that will manipulate and update data and statements that will grant permissions to users to access specific data.

Ok, first let’s recap what we have learned in our SQL lessons. Our sample database used in these lessons is named realparsmodel.

Also, With the Entity Relationship Diagram, you can see the realparsmodel database has several tables that are related to one another.

Our sample RealPars database can be developed and viewed using an ERD diagram. The ERD displays tables with relationships to each other, for example, CourseLines and Courses tables are related, using the CourseLine column name.

The ERD model may be explained in further detail in subsequent articles. This model is another way to create and visualize your database.

Having Clause

This is our last SQL statement that we have written in part 5 of this article series, using the Having clause.

The HAVING clause was used for displaying the OrderNumber column data with two calculated columns: ItemsCount and Total, created for the quantity and total amount paid for each order, only having Total greater than 1000 and the quantity greater than 600 from the OrderDetails table.

What is a SQL Subquery?

Now let’s learn about some additional common SQL clause manipulation statements. A subquery is a query nested within another query used in the SELECT, INSERT, UPDATE or DELETE statement. Also, in MySQL, a subquery can be nested inside another subquery.

Inner Query and Outer Query

A subquery is named an inner query while the query that contains the subquery is named an outer query. A subquery can be used anywhere that expression is used and must be closed in parentheses.

In this example, when the query is executed, the subquery runs first and returns all office codes of the offices located in the USA. Then, this result set is used as an input to the outer query, selecting the last name and first name columns from the employees table.

‍

Let’s try this the statement by writing it in the SQL Query Tab. Press the Execute Query button to run the subquery where statement.

The results will be displayed in the Output Panel.

EXISTS Operator

Now let’s move on, the EXISTS operator is a Boolean operator that returns either true or false. The EXISTS operator is often used in a subquery to test if data exists condition.

For example, in each row in the Students table, the query checks the StudentNumber in the Orders table. If StudentNumber, which appears in the Students table, also exists in the Orders table, the subquery returns matching rows.

As a result, the EXISTS operator returns true. Otherwise, the subquery returns no row and the EXISTS operator returns false.

Suppose you want to find the student who has placed at least one order, you would use the EXISTS operator as follows:

‍

By pressing the Execute Query button, the result shows the records of the students who have placed at least one order.

NOT EXISTS Operator

To get the student who has not placed any sales orders, you use the NOT EXISTS operator as the following statement.

‍

We can test this statement in MySQL Workbench, so press the Execute Query button to run the where not exists statement. The result will be displayed in the Output Panel with only a single student who has not placed an order.

Now we are ready to learn about additional database definition statements such as joining tables together to list columns in a result from multiple tables. This concludes the article, What are the SQL Subquery and Exists Clause statement language elements.