Advanced Querying Techniques

This tutorial delves into more sophisticated querying mechanisms, enabling you to retrieve data with greater precision and efficiency.

1. Filtering with Complex Conditions

Beyond simple equality checks, you can combine multiple conditions using logical operators like AND, OR, and NOT.

Using AND

Retrieve users who are active and belong to the 'Developers' group:

SELECT *
FROM Users
WHERE IsActive = TRUE AND UserGroup = 'Developers';

Using OR

Find users who are either administrators or have an 'Editor' role:

SELECT *
FROM Users
WHERE Role = 'Administrator' OR Role = 'Editor';

Using NOT

Select all products that are not out of stock:

SELECT *
FROM Products
WHERE NOT IsOutOfStock;

2. Sorting and Limiting Results

Control the order and the number of records returned by your queries.

Ordering Results (ORDER BY)

Sort products by price in ascending order:

SELECT ProductName, Price
FROM Products
ORDER BY Price ASC;

Sort customers by registration date in descending order:

SELECT CustomerName, RegistrationDate
FROM Customers
ORDER BY RegistrationDate DESC;

Limiting Results (LIMIT)

Get the top 5 most expensive products:

SELECT ProductName, Price
FROM Products
ORDER BY Price DESC
LIMIT 5;

3. Subqueries

Subqueries (or inner queries) are queries nested inside another query. They are useful for performing operations that require multiple steps.

Using Subqueries in WHERE Clause

Find all orders placed by customers who live in 'New York':

SELECT OrderID, OrderDate
FROM Orders
WHERE CustomerID IN (SELECT CustomerID FROM Customers WHERE City = 'New York');

Using Subqueries in FROM Clause (Derived Tables)

Calculate the average order value for customers who have placed more than 3 orders:

SELECT AVG(OrderTotal) AS AverageOrderValue
FROM (
    SELECT CustomerID, SUM(OrderAmount) AS OrderTotal
    FROM Orders
    GROUP BY CustomerID
    HAVING COUNT(OrderID) > 3
) AS CustomerOrderSummary;

4. Joining Tables

Combine data from multiple related tables. We'll briefly touch upon common join types.

INNER JOIN

Retrieve order details along with customer names:

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers
ON Orders.CustomerID = Customers.CustomerID;

LEFT JOIN

List all customers and their orders, including customers who haven't placed any orders yet:

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;

SELECT C.CustomerName
FROM Customers AS C
JOIN Orders AS O ON C.CustomerID = O.CustomerID
WHERE C.State = 'California' AND O.OrderDate > '2023-01-01'
ORDER BY C.CustomerName;

5. Wildcard Characters

Use wildcard characters with the LIKE operator for pattern matching.

• %: Represents zero or more characters.
• _: Represents a single character.

Find products whose names start with 'App':

SELECT ProductName
FROM Products
WHERE ProductName LIKE 'App%';

Find products with 'book' as the second word in their name (e.g., 'Notebook', 'Textbook'):

SELECT ProductName
FROM Products
WHERE ProductName LIKE '_%book%';

Next Steps

Explore data aggregation techniques in the next tutorial, or refer to the API documentation for detailed specifications of available query functions.