Easy to use SQL to merge queries

Two days before the interview, I found that my SQL has been forgotten... very angry, decided to make up ....

[Author: limeinan Source: Forum sorting]

 

Have you ever repeatedly searched for the required information? Sub-selection, also known as subquery, may be exactly what you are looking.SQLThis feature allows you to query results in a group, creatively limit the result group, orDatabaseIn a single call, the results are related to a non-relational query. This article will provide several examples of sub-selection and discuss how to use them.

　　Search in a result group

The sub-selection concept is simple: One selection query is placed in another query, and a resource that is unavailable in a single declaration search is created. The sub-selection allows merging of queries, and the result group comparison responsibility falls into the database rather than the application software code.

One way to use this function is to locate values in comparable data columns in two tables. For example, one of my databases has two tables: Album and Lyric. I can easily find the name of each Metallica song containing "justice" through the subquery statement below:

The following is a reference clip: 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, and then I selected all the songs containing "justice" in the lyric table. Finally, I use the IN keyword to return the song name from the Album table displayed IN the result group of the Lyric table.

I use the Lyric table result group to limit the results in the Album table. The sub-selection part in the WHERE clause is completely self-contained, so I do not need to use complete column names such as Album. song_name and Lyric. song_name. I didn't return any value from the final result group's Lyric table. If I need the song's Lyric, I will use a JOIN declaration.

　　Use not in to exclude results

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

The following is a reference clip: 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 preceding SQL code, I selected All the songs in Metallica's "And Justice for All" album, followed by All the songs with "justice" in the lyrics, finally, all the songs are returned from the Album result group that does not appear in the Lyric result group. Compared with returning two queries and comparing arrays with code, you can get the exact result through a separate statement.

　　Use EXISTS for related results

Sometimes you can access the same data in multiple ways, and you need to match (or correlation) Your results to get the value of the cross zone. For example, I can search for the Album table to get a list of Metallica songs. However, I can also get a list of Metallica songs performed by Damage, Inc from my Cover table, I can directly compare the query results in two tables to associate the values.

The following is a reference clip: 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 compare the two tables directly, not just using the result group as a passive resource. I do not return results from the Cover table. Some databases support the not exists keyword to ensure that you do NOT match.

　　Use Aggregate functions for comparison

In addition to checking data in the relevant table using the sub-selection, you can also use the aggregate function in a WHERE sub-selection to determine the master result group. For example, I want to verify the entries of each Metallica song in the Album table. In addition, I want to return the name of the album that lacks the song. Conveniently, a column in the AlbumInfo table (album_tracks) provides information on how many songs should be included.

The following is a reference clip: 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 the names of all Metallica albums whose expected number of tracks does not match the actual number of Song entries in the Album table.

　　Returns the sub-selection result.

What if I still care about the number of tracks on each album and need a comparison report? You can return the result of a sub-selection as part of the final result group. This function is often used by Aggregate functions. Generally, access to other tables can be used as part of your query. In the next example, each Metallica Album will be returned, including the number of tracks and the number of entries included in the Album table:

The following is a reference clip: 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 value of album_tracks in the AlbumInfo table to the actual number of entries in the Album table:

The following is a reference clip: UPDATE AlbumInfo SET album_tracks = Select count (*) FROM Album WHERE AlbumInfo. album_name = Album. album_name) WHERE AlbumInfo. band_name = 'metallica ';

In the preceding two examples, the sub-selection statement is executed as a self-contained unit.

　　Subselection comparison keyword (ALL, SOME, ANY)

In addition to the standard query function, there are three keywords that allow you to compare an expression value with the result group declared by a Selection Sub-statement. These keywords return TRUE or FALSE Boolean values. The ALL keyword requires that ALL values in the sub-selection comply with the comparison operator. SOME and ANY keywords require at least one pair. Here is a simple example of the ALL keyword.

The following is a reference clip: SELECT * FROM AlbumSales WHERE album_gross> ALL (SELECT album_costs FROM AlbumProduction );

The above example will return from the AlbumSales table all records in the AlbumProduction table that produce the most expensive album with a total cost greater than the cost. If ANY is used to replace ALL, the statement returns ALL album records with a total cost greater than the minimum album cost. The Declaration = any and in keywords have the same meaning. Declaration <> the ALL and not in keywords are equivalent. The keyword ANY is equivalent to SOME. Database manufacturers have different support for these keywords. Therefore, you should trust the manufacturer's information in case of problems.