Powerful SQL and The magical Subqueries

SQL (Structured query language) is one of the most sought after, important and valuable skill employers desire. In a business environment, interacting with data is inevitable. Data is databases, and, to access those databases, you need SQL. Organizations are looking for individuals who have mastered the skills of accessing and analyzing data using SQL. So, with that in mind, Let’s answer the key question,

What is SQL?

SQL is Structured Query Language, an ANSI (American National Standards Institute) Standard language for accessing databases. SQL is used to analyze and understand rows of data stored in tables contained within a database. SQL queries can be used to retrieve large amounts of data from a database.

Magical concept in SQL called Subqueries

As the name implies, Subquery is a query inside a query. Subqueries may be nested inside DML(Data modification language — Insert, Delete, update, Select) statements or inside another subquery. Subqueries are also known as inner queries or nested queries where inner query is executed first.

SubQuery

Subqueries may occur in

1. WHERE clause :
   This is used when you need to compare one or more results returned by the inner query to the outer query using

• IN/ NOT IN
• EXISTS/ NOT EXISTS
• ALL/ANY
• SOME
• Other Operators along with the comparison operators =, >, >=, <, <=

SELECT * 
FROM outer_table
WHERE col_name IN (
                 Subquery
                  );

2. FROM clause :

This is used when you want the inner query to act as a table, Where in you can select the columns from inner table by giving it an alias, and use it in outer select clause. Subquery may also be joined with outer query using the FROM clause.

SELECT inner_id, blogtotal
FROM   
    (SELECT inner_id,SUM(blogs) AS blogtotal
     FROM  table_name
     GROUP BY inner_id
    ) AS innertable
ORDER BY blogtotal;

3. SELECT clause :

You use subquery in a select clause when you want the result of subquery to be included as a column.

SELECT col1, col2,(SELECT AVG(col3)
                 FROM inner_table
                ) AS avgcol3
FROM outer_table;

What results can Subqueries return?

1. Zero or one value: You get a single value result when the subquery is evaluated only once and the value is returned to the outer query. Usually an aggregate function is used in the query.

2. One or more values: You get a list of values when the subquery is evaluated once and then the values are returned to the outer query. Usually WHERE clause with IN operator is used in the query.

3. One or more rows/ column: You get rows of values when the subquery is evaluated once and then the row/rows of values is returned to outer query. Usually WHERE clause with IN operator or comparison operators =, >, >=, <, <= is used in the query

Image courtesy : sql-subqueries.gif

Types of subqueries

1. Nested subqueries

In nested subqueries, a subquery can include one or more subqueries.

SELECT bid, bname
FROM blog
WHERE bid IN
            (SELECT bid
             FROM article
             WHERE id IN
                    (SELECT id
                     FROM persons
                     WHERE noofblog > 10
                    )
            );

Result of Above Nested Subquery

2. Correlated Subqueries :

In Correlated Subqueries, inner query uses values from the outer query. That is, inner query is executed for every row of outer query.

SELECT bid, bname
FROM blog
WHERE EXISTS
           (SELECT bid
            FROM article
            WHERE bid = id
            and aname LIKE 's%'
            );

Result of Above Correlated Subquery

Rules for Subquery

• Subqueries must be enclosed in parenthesis.
• While writing SELECT list with comparison operator for a subquery, only one column name should be included .
• In the WHERE clause, the column name of an outer query must be join compatible with column of SELECT list in the subquery, in the WHERE clause.
• If the WHERE clause is used with comparison operator and the subquery returns NULL than the outer query will not return any rows.
• Subqueries not written with ANY, ALL, SOME keywords cannot include GROUP BY and HAVING clause as they are supposed to return single value.
• ORDER BY clause cannot be used with subquery unless specified with TOP (in TSQL).

Conclusion

Subqueries are a handy, flexible and resourceful tool. You can use the data returned by the Subquery in your outer query to make them more useful for various applications.
You learnt about a very useful concept in SQL here. But there is so much more to learn in SQL. Whether you are a beginner or already working with data, SQL is definitely going to help you on your career path. Technology is evolving and so should we.