Start Coding

Subjects

JAVASCRIPTHTMLCSSPYTHONSQLJAVACPPCSHARPPHPTYPESCRIPTCRUBYGOSWIFTKOTLINRUSTRBASHSCALAOBJECTIVE-CDARTPERLLUAMATLABXMLJSONMARKDOWNASSEMBLYLATEXYAMLSOLIDITYBLOCKCHAIN

Topics

Introduction to SQL

What is SQL?SQL HistorySQL StandardsSQL vs NoSQLSQL Database Management Systems

SQL Basics

SQL SyntaxSQL CommentsSQL Data TypesSQL OperatorsSQL ExpressionsSQL LiteralsSQL IdentifiersSQL KeywordsSQL Case Sensitivity

SQL Database Fundamentals

Database ConceptsTables and FieldsPrimary KeysForeign KeysIndexesConstraintsNormalizationDenormalizationSQL Schema Design

SQL Data Manipulation

SQL SELECT StatementSQL INSERT StatementSQL UPDATE StatementSQL DELETE StatementSQL MERGE StatementSQL AliasesSQL WildcardsSQL LIKE OperatorSQL IN OperatorSQL BETWEEN OperatorSQL NULL ValuesSQL ORDER BY ClauseSQL LIMIT ClauseSQL OFFSET Clause

SQL Filtering and Sorting

SQL WHERE ClauseSQL AND, OR, NOT OperatorsSQL GROUP BY ClauseSQL HAVING ClauseSQL DISTINCT KeywordSQL TOP Clause

SQL Joins

SQL INNER JOINSQL LEFT JOINSQL RIGHT JOINSQL FULL OUTER JOINSQL CROSS JOINSQL Self JOINSQL Natural JOINSQL Multiple JOINs

SQL Functions

SQL Aggregate FunctionsSQL Scalar FunctionsSQL String FunctionsSQL Numeric FunctionsSQL Date FunctionsSQL Conversion FunctionsSQL System FunctionsSQL User-Defined Functions

SQL Subqueries

SQL Subquery BasicsCorrelated SubqueriesNested SubqueriesSQL EXISTS OperatorSQL ANY and ALL OperatorsSubqueries in SELECTSubqueries in FROMSubqueries in WHERE

SQL Views

Creating SQL ViewsAltering SQL ViewsDropping SQL ViewsUpdatable ViewsMaterialized ViewsView Limitations

SQL Transactions

SQL Transaction BasicsACID PropertiesBEGIN TRANSACTIONCOMMITROLLBACKSAVEPOINTTransaction Isolation Levels

SQL Data Definition

SQL CREATE TABLESQL ALTER TABLESQL DROP TABLESQL TRUNCATE TABLESQL CREATE INDEXSQL DROP INDEXSQL CREATE DATABASESQL ALTER DATABASESQL DROP DATABASE

SQL Data Control

SQL GRANTSQL REVOKESQL RolesSQL UsersSQL Privileges

SQL Performance Optimization

SQL Query OptimizationSQL Execution PlansSQL Index OptimizationSQL Query TuningSQL CachingSQL Partitioning

Advanced SQL Concepts

SQL Stored ProceduresSQL TriggersSQL CursorsSQL Common Table ExpressionsSQL Window FunctionsSQL Pivot and UnpivotSQL JSON OperationsSQL XML OperationsSQL Full-Text Search

SQL Best Practices

SQL Naming ConventionsSQL Code OrganizationSQL Error HandlingSQL Security Best PracticesSQL Backup and RecoverySQL Version Control

SQL Subquery Basics

Subqueries are a powerful feature in SQL that allow you to nest one query within another. They provide a way to perform complex operations and retrieve data based on the results of another query.

What is a Subquery?

A subquery, also known as a nested query or inner query, is a SELECT statement embedded within another SQL statement. It can be used in various parts of a main query, such as the SELECT, FROM, or WHERE clauses.

Basic Syntax

The general structure of a subquery is as follows:

SELECT column1, column2, ...
FROM table_name
WHERE column_name operator (SELECT column_name FROM table_name WHERE condition);

The subquery is enclosed in parentheses and typically appears on the right side of a comparison operator.

Types of Subqueries

1. Single-row Subquery

Returns only one row and can be used with single-row operators like =, >, <, >=, <=, and <>.

2. Multiple-row Subquery

Returns multiple rows and is used with multiple-row operators such as IN, ANY, or ALL.

3. Correlated Subquery

References columns from the outer query and is executed once for each row in the outer query.

Examples

Example 1: Single-row Subquery

SELECT employee_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

This query selects employees whose salary is above the average salary.

Example 2: Multiple-row Subquery

SELECT product_name, price
FROM products
WHERE product_id IN (SELECT product_id FROM orders WHERE order_date = '2023-05-01');

This query retrieves products that were ordered on a specific date.

Best Practices

  • Use subqueries sparingly, as they can impact performance on large datasets.
  • Consider using SQL joins as an alternative when appropriate.
  • Ensure that the subquery returns the expected number of rows (single or multiple).
  • Test subqueries independently before incorporating them into the main query.

Common Use Cases

Subqueries are particularly useful for:

  • Filtering data based on aggregate results
  • Performing complex comparisons
  • Creating derived tables in the FROM clause
  • Implementing dynamic filtering conditions

Limitations and Considerations

While subqueries are versatile, they have some limitations:

  • Performance may degrade with deeply nested subqueries
  • Some databases have restrictions on subquery placement or depth
  • Correlated subqueries can be slow on large datasets

Understanding these basics will help you leverage subqueries effectively in your SQL queries, enabling more complex and powerful data retrieval operations.