Ever found yourself needing to update a bunch of rows in your database, but the condition for the update depends on data from another table? That’s where UPDATE with a subquery comes in handy. Let’s break it down.
Tag: create query
Understanding the SQL Subquery
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 readingHow to Return Values in SQL Without using SELECT
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.
2 Ways to Return Data in MySQL without using SELECT
How to Select Data from an Invisible Column in MySQL
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.
How the EXCEPT Clause Works in MySQL
When combining multiple query blocks in MySQL, we can use the EXCEPT clause to return just those rows from the first block that are not present in the second block.
Understanding the INTERSECT Clause in MySQL
When combining multiple query blocks in MySQL, we can use the INTERSECT clause to return just those rows that are common to both query blocks.
It’s a bit like the UNION clause, except that it excludes rows that aren’t present in both queries.
2 Ways to Rename the Columns when using VALUES to Create a Constant Table in PostgreSQL
When we use the VALUES statement to create a constant table, PostgreSQL automatically names the columns column1, column2, etc. This saves us from having to provide names for the columns or from getting a blank column header.
But we also have the option of naming the columns ourselves.
Continue readingHow to Rename the Columns Returned by the VALUES Statement in SQLite
If you’ve ever used the VALUES clause as a stand alone statement, you may have noticed that SQLite provides default column names for the results. SQLite conveniently names them column1, column2, and so on.
However as convenient as this is, you might want to provide names that are more meaningful.
Fortunately there’s an easy way to do that.
Continue readingUsing VALUES instead of SELECT in SQLite
Some database management systems, including SQLite, allow us to use the VALUES keyword as a stand alone SQL statement. So we can use VALUES to return a constant table, usually with less code than would be required if we were to use the SELECT statement.
The following examples demonstrate how.
Continue reading