SQL (Structured Query Language)

Index

SQL Join

SQL CLAUSES

SUB-QUERY in SQL

SUB-QUERY using EXISTS

SQL (Structured Query Language) is a programming language used to manage and manipulate relational databases. It is used to insert, update, and retrieve data from a database.

Some common operations performed in SQL include:

  1. SELECT - used to retrieve data from one or more tables in a database. For example, to select all the columns and rows from a table named customers, the SQL query would be:

SELECT * FROM customers;

  1. INSERT - used to insert new data into a table. For example, to insert a new customer into the customers table with the values John Smith, [email protected], and 555-555-5555, the SQL query would be:

INSERT INTO customers (name, email, phone) 
VALUES ('John Smith', '[email protected]', '555-555-5555');

  1. UPDATE - used to modify existing data in a table. For example, to update the phone number for a customer named John Smith in the customers table to 555-555-5555, the SQL query would be

UPDATE customers SET phone = '555-555-5555' 
WHERE name = 'John Smith';

  1. DELETE - used to delete data from a table. For example, to delete the customer named John Smith from the customers table, the SQL query would be:

DELETE FROM customers WHERE name = 'John Smith';

  1. JOIN - used to combine rows from two or more tables based on a related column between them. For example, to select all the columns from the orders table and the corresponding customer name from the customers table, the SQL query would be:

SELECT orders.*, customers.name 
FROM orders 
JOIN customers ON orders.customer_id = customers.id;

  1. CREATE - used to create a new table. For example, to create a new table named employees with columns for id, name, email, and department, the SQL query would be:

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(255),
  email VARCHAR(255),
  department VARCHAR(255)
);

  1. ALTER - used to add, modify or delete columns in a table. For example, to add a new column named address to the employees table, the SQL query would be:

ALTER TABLE employees ADD address VARCHAR(255);

  1. DROP - used to delete a table. For example, to delete the employees table, the SQL query would be:

DROP TABLE employees;

SQL Join

There are several types of SQL joins, each with a specific use case:

  1. INNER JOIN - Returns only the rows that have matching values in both tables. This is the most common type of join and is the default when using the JOIN keyword. For example, to select all the orders and the corresponding customer information from the orders and customers tables, the SQL query would be:

SELECT orders.*, customers.*
FROM orders
INNER JOIN customers ON orders.customer_id = customers.id;

  1. LEFT JOIN - Returns all the rows from the left table (in this case, the orders table), and any matching rows from the right table (in this case, the orders table). If there is no match, the right table's columns will contain NULL values. For example, to select all the orders and the corresponding customer information, but also include orders that don't have a matching customer, the SQL query would be:

SELECT orders.*, customers.*
FROM orders
LEFT JOIN customers ON orders.customer_id = customers.id;

  1. RIGHT JOIN - Returns all the rows from the right table (in this case, the customers table), and any matching rows from the left table (in this case, the orders table). If there is no match, the left table's columns will contain NULL values. For example, to select all the customer information and the corresponding order information, but also include customers that don't have any orders, the SQL query would be:

SELECT customers.*, orders.*
FROM customers
RIGHT JOIN orders ON orders.customer_id = customers.id;

  1. FULL OUTER JOIN - Returns all the rows from both tables, and any matching rows. If there is no match, the columns from the non-matching table will contain NULL values. For example, to select all the customer information and the corresponding order information, but also include customers that don't have any orders and orders that don't have any customers, the SQL query would be:

SELECT customers.*, orders.*
FROM customers
FULL OUTER JOIN orders ON orders.customer_id = customers.id;

  1. CROSS JOIN - Returns the Cartesian product of the two tables, meaning it returns every possible combination of rows from both tables. This type of join is not used as often and can be less performant than other joins, because it can produce a large number of rows. For example, to select all the possible combinations of customers and orders, the SQL query would be:

SELECT customers.*, orders.*
FROM customers
CROSS JOIN orders;

  1. SELF JOIN - A self join is a type of join where a table is joined to itself. This is useful when you need to compare or combine data within the same table.

  • For example, let's say you have a "employees" table that has columns for "id," "name," "manager_id," and "department." The "manager_id" column references the "id" column of the same table, indicating which employee is the manager of another employee.

  • To select all the employees and their corresponding managers, you can use a self join. The SQL query would be:

SELECT e1.name as employee, e2.name as manager
FROM employees e1
JOIN employees e2 ON e1.manager_id = e2.id;

Here, the table "employees" is joined to itself twice, using aliases "e1" and "e2" to distinguish the two instances of the table. This query will return the name of all employees and the name of their manager. If an employee doesn't have a manager, the manager's name will be null.

Self joins can also be useful when you want to find hierarchical data like in above example, or when you want to compare values within the same table, such as finding duplicate values.

SQL CLAUSES

SQL has several clauses that can be used to filter, sort, and organize the data returned by a query. Some of the most common clauses are:

  1. WHERE - used to filter the rows returned by a query based on a specific condition. For example, to select all the orders with a total amount greater than 100 from the orders table, the SQL query would be:

SELECT *
FROM orders
WHERE total > 100;

  1. GROUP BY - used to group the rows returned by a query based on one or more columns. For example, to select the total sales for each department from the sales table, the SQL query would be:

SELECT department, SUM(sales) as total_sales
FROM sales
GROUP BY department;

  1. HAVING - used to filter the groups returned by a GROUP BY clause based on a specific condition. For example, to select the total sales for each department, but only for departments with more than 1000 in total sales, the SQL query would be:

SELECT department, SUM(sales) as total_sales
FROM sales
GROUP BY department
HAVING SUM(sales) > 1000;

  1. ORDER BY - used to sort the rows returned by a query based on one or more columns. For example, to select all the customers from the "customers" table, sorted by last name, the SQL query would be:

SELECT *
FROM customers
ORDER BY last_name;

  1. LIMIT - used to limit the number of rows returned by a query. For example, to select the top 10 most recent orders from the "orders" table, the SQL query would be:

SELECT *
FROM orders
ORDER BY order_date DESC
LIMIT 10;

  1. DISTINCT - used to select unique values from a column. For example, to select the unique department names from the "employees" table, the SQL query would be:

SELECT DISTINCT department
FROM employees;

These are some of the most common clauses in SQL. By combining these clauses, you can create complex queries that filter, group, sort, and limit your data in various ways.

SUB-QUERY in SQL

A subquery in SQL is a query that is nested within another query. It is used to return a set of results that can be used in the outer query. Subqueries can be used in various parts of a SQL statement, such as the SELECT, FROM, WHERE and HAVING clauses.

Here is an example of how a subquery can be used in the WHERE clause:

SELECT *
FROM orders
WHERE customer_id IN (SELECT id 
                    FROM customers 
                    WHERE country = 'USA');

In this example, the subquery (SELECT id FROM customers WHERE country = 'USA') is used to return a set of customer IDs for customers in the USA, and the outer query SELECT * FROM orders uses that set of IDs to return all the orders for customers in the USA.

Here is another example of how a subquery can be used in the SELECT clause:

SELECT name, (SELECT COUNT(*) 
            FROM orders 
            WHERE customer_id = customers.id) as total_orders
FROM customers;

In this example, the subquery (SELECT COUNT(*) FROM orders WHERE customer_id = customers.id) is used to return the total number of orders for each customer, and the outer query SELECT name, (subquery) as total_orders FROM customers uses that information to return the name and total orders for each customer.

A subquery can also be used in the FROM clause as well, which is known as derived table or subselect from clause. For example

SELECT name, department 
FROM (SELECT name, department, salary 
    FROM employees) 
WHERE salary > 50000;

In this example, the subquery (SELECT name, department, salary FROM employees) is used to return the name, department and salary of all employees and the outer query SELECT name, department FROM (subquery) WHERE salary > 50000 uses that information to return the name and department of employee who have salary more than 50000.

Subqueries can be a powerful tool in SQL and can be used to create complex and efficient queries. However, it's important to be aware that using too many subqueries or nesting them too deeply can make your queries complex and less efficient.

SOME MORE EXAMPLE

A multiple-row subquery is a subquery that returns more than one row. This type of subquery can be used in various parts of a SQL statement, such as the SELECT, FROM, WHERE and HAVING clauses.

Here is an example of how a multiple-row subquery can be used in the WHERE clause:

SELECT *
FROM orders
WHERE customer_id IN (SELECT id 
                    FROM customers 
                    WHERE country IN ('USA', 'Canada'));

In this example, the subquery (SELECT id FROM customers WHERE country IN ('USA', 'Canada')) is used to return a set of customer IDs for customers in the USA and Canada, and the outer query SELECT * FROM orders uses that set of IDs to return all the orders for customers in the USA and Canada.

Here is another example of how a multiple-row subquery can be used in the SELECT clause:

SELECT name, (SELECT SUM(price) 
            FROM order_items 
            WHERE order_id IN (SELECT id 
                            FROM orders 
                            WHERE customer_id = customers.id)) 
                            as total_spent
FROM customers;

In this example, the subquery (SELECT SUM(price) FROM order_items WHERE order_id IN (SELECT id FROM orders WHERE customer_id = customers.id)) is used to return the total spent by each customer by summing up prices of all order items that belong to orders of that customer and the outer query SELECT name, (subquery) as total_spent FROM customers uses that information to return the name and total spent for each customer.

Here is another example of how a multiple-row subquery can be used in the FROM clause:

SELECT name, department 
FROM employees 
WHERE department IN (SELECT department 
                    FROM employees WHERE salary > 50000);

In this example, the subquery (SELECT department FROM employees WHERE salary > 50000) is used to return all departments where at least one employee has salary more than 50000, and the outer query SELECT name, department FROM employees WHERE department IN (subquery) uses that information to return the name and department of all employees who are working in departments where at least one employee has salary more than 50000.

As you can see, multiple-row subqueries can be used in various ways in SQL to return sets of data that can be used in the outer query. It's important to note that using multiple-row subqueries can make your queries complex and less efficient, so it's important to use them judiciously and test their performance.

SUB-QUERY using EXISTS

In SQL, the EXISTS operator is used to test whether a subquery returns any rows. It is often used in conjunction with a subquery in the WHERE clause to filter rows based on the existence of related data in another table.

Here is an example of how the EXISTS operator can be used in a subquery:

SELECT *
FROM orders
WHERE EXISTS (SELECT * FROM order_items WHERE order_id = orders.id);

PreviousNormalizationNextPostgreSQL

Last updated

Was this helpful?