Follow me. sql: (iii) Merging queries using SQL sub-selection

Have you ever queried for the information you need? Sub-Select, also known as subqueries, may be exactly what you are looking for. This feature of SQL allows you to query in a set of results, creatively qualify the result group, or relate the result to an unrelated query in a single call to the database. In this article I will give examples of several sub selections and discuss when to use them.

Search in a result group

The idea of a sub-selection is simple: A select query is placed inside another query to create a resource that is not available in a single declaration search. Sub-selection allows the merging of queries, and the responsibility of the result group comparison falls in the database rather than in the application software code.

One way to use this feature is to position the values in the comparable data columns in the two tables. For example, one of my databases has two tables, album and lyric. I can easily find the name of "Justice" in each Metallica song by following the subquery declaration:

SELECT Song_name from Album
WHERE band_name = ' Metallica '
and Song_name in
(SELECT song_name from lyric
WHERE song_lyric like '%justice% ');

This example is very simple, I selected all Metallica songs from the album table, then I select all the songs that contain "Justice" in the lyric table, and finally, I use the IN keyword to return the song name from the album table displayed in the Lyric table result group.

I use the lyric table result group to qualify the results in the album table. The child selection section in the WHERE clause is fully self-contained, so I don't need to use a complete column name such as Album.song_name and Lyric.song_name. I didn't return any values from the Lyric table in the final result group, and if I needed the song's lyric, I would use a join declaration.

Excluding results using not in
You can use the not in keyword to obtain results that are explicitly not included in another result group. For example, I want to return a song that Metallica does not contain the word "Justice" on the "and Justice for All" album by using the following code:

SELECT Song_name from Album
WHERE album_name = ' and Justice for all '
and band_name = ' Metallica '
and Song_name not in
(SELECT song_name from lyric
WHERE song_lyric like '%justice% ');

In the previous SQL code, I chose Metallica's "and Justice for all," and then all the songs in the album with "Justice" in the lyrics, and finally all the songs from the album result group that did not appear in the lyric result group. Instead of returning two queries and using code to compare arrays, you can get the exact results with a single declaration.

Use exists to correlate results
Sometimes you can access the same data in a variety of ways, and you need to match (or correlate) your results to get a cross section of the value. For example, I can get a list of Metallica songs by searching the album table, but I can also get a list of Metallica songs from my cover table from Damage, Inc., where I can compare the query results directly in two tables to correlate the values.

SELECT Album.song_name from Album
WHERE album.band_name = ' Metallica '
and EXISTS
(SELECT cover.song_name from Cover
WHERE cover.band_name = ' Damage, Inc. '
and cover.song_name = Album.song_name);

In the SQL code, I use the full column name because I'm comparing the two tables directly, not just using the result group as a passive resource. I do not return the result from the cover table. Some databases support the NOT EXISTS keyword to ensure that you do not match.

Use aggregate functions to compare

In addition to using child selection to check data in related tables, you can also use aggregate functions in a where to determine the main result group. For example, I want to verify the entries in the album table for every Metallica song. Also, I want to return the name of the album that lacks the song. Conveniently, the Albuminfo table contains a column (album_tracks) that gives you information about how many songs you should have.

SELECT Albuminfo.album_name from Albuminfo
WHERE albuminfo.band_name = ' Metallica '
and Album_tracks <>
(SELECT COUNT (*) from Album
WHERE album.album_name = albuminfo.album_name);

Now I have successfully returned to all Metallica albums, the number of tracks and the album name that does not match the number of actual song entries in the album table.

Returns the child selection result
What if I care about the number of tracks on each album and need to get a comparison report? You can return the result of a child selection as part of the final result group. This function is often used by aggregate functions. Typically, access to other forms can be part of your query. The next example will return each Metallica album, the number of tracks to include and the number of entries included in the album Table:

SELECT Albuminfo.album_name, Album_tracks,
(SELECT COUNT (*) from Album
WHERE album.album_name = albuminfo.album_name)
From Albuminfo
WHERE albuminfo.band_name = ' Metallica ';

Another powerful example involves changing the album_tracks value in the Albuminfo table to the actual number of entries in the album table:

UPDATE albuminfo SET album_tracks =
SELECT COUNT (*) from Album
WHERE albuminfo.album_name = album.album_name)
WHERE albuminfo.band_name = ' Metallica ';

The sub-selection declaration in the last two examples is considered to be a self-contained unit for execution.

Sub-select comparison keywords (all, SOME, any)
In addition to using the standard query functionality, there are three more keywords that allow you to compare an expression value to a single column selection declaration result group, which returns a Boolean value of TRUE or false. The all keyword requires that all values in the child selection follow the comparison operator. The some and any keywords require at least one pair. This is a simple example of the ALL keyword.

SELECT * from Albumsales
WHERE Album_gross >
All (SELECT album_costs from albumproduction);

The example above will return all records of the most expensive albums produced in the Albumproduction table from the Albumsales table in which the total amount is greater than the cost. If you replace all with any, the Declaration returns all album records that have a total payout greater than the lowest album cost. The declaration = Any is the same as the in keyword meaning. The declaration <> all are equivalent to the not in keyword. The keyword any and some are also equivalent. The database manufacturer's support for these keywords is different, so be sure to check the manufacturer's information when problems arise.

Who has questions about standardized data structures?
Sub-selection query syntax is straightforward, and knowing when to use it is a difficult task. If you've ever had problems with standardizing your data structure, the sub-selection statement will help you get a deeper understanding.