SQL - SubQuery Part #2

In today’s Newsletter,

1. SQL - SubQuery explain

2. Learn by doing - Data science

3. AI & TECH NEWS

A subquery is a SQL query nested inside another query. It is also known as an inner query or nested query. The result of the subquery is used by the outer query to filter, compute, or manipulate data.

Example:

SELECT employee_name, salary
FROM employees
WHERE department_id = (SELECT department_id FROM departments WHERE department_name = 'IT');

In this example, the subquery fetches the department ID of 'IT' from the departments table, which is then used by the outer query to find employees in that department.

Applications of Subqueries

• Filtering data dynamically

• Performing aggregate calculations

• Comparing results across tables

• Checking existence of records

• Fetching specific data without joining multiple tables

Types of Subqueries

1. Single-row Subquery – Returns a single value.

2. Multi-row Subquery – Returns multiple values.

3. Multi-column Subquery – Returns multiple columns.

4. Correlated Subquery – Uses values from the outer query in its execution.

5. Nested Subquery – A subquery inside another subquery.

Importance of Subqueries

• Helps in breaking complex queries into simpler components.

• Eliminates the need for temporary tables.

• Provides a way to dynamically filter data.

• Enhances readability and maintainability of queries.

Where to Use Subqueries?

• Inside SELECT, FROM, or WHERE clauses.

• When you need to compare a value with multiple records.

• In filtering results dynamically.

• When using EXISTS, IN, ANY, ALL operators.

Understanding Where to Use a Subquery

• If a query needs additional filtering from another table without joining, use a subquery in the WHERE clause.

• If you need computed data from another table, use a subquery in the SELECT clause.

• If the dataset needs to be used as a virtual table, use a subquery in the FROM clause.

Rules of Using Subqueries

• Enclose subqueries in parentheses.

• A subquery should return compatible data type values when used in a comparison.

• Use EXISTS or IN for multiple results.

• Avoid using subqueries where joins can perform better for large datasets.

Example Using Interrelated Tables

Consider three tables:

1. employees (employee_id, employee_name, salary, department_id)

2. departments (department_id, department_name)

3. projects (project_id, employee_id, project_name)

Query: Find employees working on 'AI Project' along with their department names.

SELECT employee_name, salary, (SELECT department_name FROM departments d WHERE e.department_id = d.department_id) AS department_name
FROM employees e
WHERE employee_id IN (SELECT employee_id FROM projects WHERE project_name = 'AI Project');

Explanation:

• The inner query fetches employee IDs who are working on 'AI Project'.

• The outer query retrieves their names and salaries.

• A nested subquery fetches the department name.

Subqueries are powerful tools in SQL that help in retrieving and manipulating data efficiently. Understanding when and how to use them enhances your database querying capabilities. Always evaluate whether a subquery or a join is the best approach based on performance needs.

Learn by doing =>

There’s a reason 400,000 professionals read this daily.

Join The AI Report, trusted by 400,000+ professionals at Google, Microsoft, and OpenAI. Get daily insights, tools, and strategies to master practical AI skills that drive results.

Sign up now for free and work smarter, not harder.

#1. Google Launches New AI Models That Let Robots Do Physical Tasks Without Training (Source)

Google has launched two new AI models for robots based on its Gemini 2.0 technology. These models allow robots to perform physical tasks without prior training.

One model, Gemini Robotics, enables robots to take physical actions, while Gemini Robotics-ER helps them understand their surroundings and supports custom programming. The models are designed for various robots, including humanoids and industrial machines.

This comes a month after Figure AI ended its partnership with OpenAI due to an internal AI breakthrough. Google tested its models on its ALOHA 2 robotics platform and says they can be adapted for advanced use cases like Apptronik's Apollo robot, which recently secured $350 million in funding.

#2. OpenAI launches new tools to help businesses build AI agents (Source)

OpenAI has launched new tools to help businesses and developers build AI agents—automated systems that complete tasks independently. These tools are part of the Responses API, which replaces the Assistants API and enables AI agents to search the web, scan files, and navigate websites.

The API includes GPT-4o search models, which provide fact-based answers, and a Computer-Using Agent (CUA) that can automate tasks like data entry and app workflows. While promising, OpenAI acknowledges that AI agents still have limitations, including occasional inaccuracies and navigation challenges.

Alongside the API, OpenAI is releasing the Agents SDK, an open-source toolkit for integrating AI models, adding safeguards, and monitoring agent performance.

Need help with learning everything about SQL? Send me a message on WhatsApp: +917891351553

Thanks for your time and consideration,

Shailesh and NextCareerStep team