In PostgreSQL, a composite value is stored as a comma separated list of values, surrounded by parentheses.
When we have a table that contains composite values, we can extract individual fields by using dot notation to refer to the specific field that we want to extract.
Continue readingIn SQL, it’s not unusual to see subqueries in SELECT statements, where they can help narrow down the results based on a complex condition.
But the SELECT statement isn’t the only place we can put a subquery.
We can use subqueries in an UPDATE statement, and we can update multiple rows too, if required.
In this article, we look at how to update multiple columns by using a SQL subquery in the UPDATE statement.
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 readingWhen 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.
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.
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 readingThe 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.
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.
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.