Tag: create query

Understanding the Correlated Subquery in SQL

Posted on by Ian

When writing SQL queries, we sometimes find ourselves including a subquery in the query in order to get the results we want. Sometimes the subquery we include could be taken out of that query and run by itself to return its own independent result set. But not the correlated subquery.

A correlated subquery relies on the outer query for its data. We can’t simply move it outside of the outer query and expect it to work.

Let’s take a look the correlated subquery in SQL and observe it in action with a few examples.

Continue reading →

Examples of SQL Subqueries in the FROM Clause

Posted on by Ian

When used in the FROM clause, a SQL subquery creates a temporary table that can be queried like any other table. These queries are sometimes called derived tables or table expressions because the outer query uses the results of the subquery as a data source.

In this article we look at three different examples of SQL subqueries that are placed into the FROM clause.

Continue reading →

Understanding the SQL Subquery

Posted on by Ian

One of the things I love about SQL is just how easy it is to write a simple query that returns a meaningful result set, without having to a whole bunch of complex code. And I imagine most beginners are relieved when they discover this too.

However, while basic queries can go a long way, there’s a whole world of advanced techniques that can take our SQL development to another level. Somewhere in that world, would be the SQL subquery.

In this article, we look at the SQL subquery. We’ll explore what subqueries are, how they work, and when to use them. We’ll also look at some simple examples to demonstrate their use.

Continue reading →

How to Return Values in SQL Without using SELECT

Posted on by Ian

The SQL SELECT statement is possibly the most commonly used SQL statement. It’s often used to return data from a database, but it can also be used to call functions that return data. The SELECT statement can also be used to return static values, such as string literals.

But the SELECT statement isn’t the only way we can return data in SQL. Another way to return values is with the VALUES statement.

Continue reading →

How to Select Data from an Invisible Column in MySQL

Posted on by Ian

From MySQL 8.0.23 we’ve had the ability to create invisible columns. But with that comes a potential “gotcha” with our existing queries, and even with new queries we write, depending on how we write them.

The issue is that invisible columns are not returned whenever we use the asterisk wildcard (*) to select data. One of the most common ways to start a query is with SELECT *. This typically selects all columns from the table.

But it doesn’t select invisible columns.

So if we want to include invisible columns, we need to explicitly include them in our SELECT list.

Continue reading →