fbpx

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

Learn about some of the advanced data definition SQL statements.

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.

Next SQL Lessons

Our series of subsequent articles to follow will consist of the following lessons.

Be sure to read for these articles offering prerequisite learning for the beginner and then unto the more advanced statements of SQL learning.

The next SQL lessons soon to be available are:

Create Table Clause statement language element

– Cross Join, Inner Join, and Union Clause statement language elements

If you would like to get additional training on a similar subject please let us know in the comment section.

Check back with us soon for more automation control topics.

Got a friend, client, or colleague who could use some of this information? Please share this article.

The RealPars Team
By Wally Gastreich

By Wally Gastreich

Automation Engineer

Posted on Feb 17, 2020

By Wally Gastreich

Automation Engineer

Posted on Feb 17, 2020

5 Actionable Tips for Getting a PLC Programming Job with NO Experience

5 Actionable Tips for Getting a PLC Programming Job with NO Experience

In this blog post, you’ll learn about the mindset that helped me getting a PLC programming job with NO experience. This is my personal experience as someone who searched for a job in this field and as an employer who reviews resumes and interviews candidates for a variety of projects. So let’s get started!

A First Look at the Low-Code Future of PLC Programming

A First Look at the Low-Code Future of PLC Programming

At RealPars, we focus on teaching automation engineers, controls engineers, and technicians the skills that they need to be successful in their careers both now and in the future. We are constantly collaborating with manufacturers to understand what the future of...

Omron Sysmac Studio 3D Simulation Visualization

Omron Sysmac Studio 3D Simulation Visualization

 In this article, you're going to learn how to use Omron Sysmac Studio 3D simulation function for your robatic applications. The 3D function is easily added to Sysmac Studio by way of a license from OMRON. We’ll give you more details on that later.Simulation of a...

RealPars is the world's largest online learning platform for cutting-edge industrial technologies. 

Questions?
[email protected]
+31 10 316 6400
Mon - Fri  8:30 am to 5:30 pm (CET)

Rotterdam Science Tower,
Marconistraat 16,
3029AK Rotterdam, The Netherlands

COMPANY

About

Job openings

Privacy

Cookies

LEARN

Courses

Blog

Sign in

HELP

Contact Us

Help & Support

Refund & Cancellation Policy

© 2022 RealPars B.V. All rights reserved.

Created with coffee and tea in Rotterdam.