Data Query Syntax Analysis DQL

DQL (Data Query Language), Data Query Statement.

SELECT [ALL|DISTINCT]<target column expression>[,<target column expression>]...
    FROM <table name or view name>[,<name or view name>]
    [WHERE<condition expression>]
    [GROUP BY<column name 1>[HAVING<condition expression>]
    [ORDER BY<column name 2>[ASC|DESC]...]

Joins

Inner Join

An inner join, also known as an equijoin, uses the INNER JOIN keyword.

SELECT A.value, B.value
FROM tablea AS A INNER JOIN tableb AS B
ON A.key = B.key;

You can omit the explicit use of INNER JOIN by using a standard query and joining the columns to be connected in both tables using an equijoin within the WHERE clause.

SELECT A.value, B.value
FROM tablea AS A, tableb AS B
WHERE A.key = B.key;

Self Join

A self join can be viewed as a type of inner join where the joined table is the same table itself.
Example: An employee table containing employee names and their department. The goal is to find all employee names who are in the same department as Jim.

-- Subquery version
SELECT name
FROM employee
WHERE department = (
      SELECT department
      FROM employee
      WHERE name = "Jim")
-- Self join version
SELECT e1.name
FROM employee AS e1 INNER JOIN employee AS e2
ON e1.department = e2.department
      AND e2.name = "Jim";

Natural Join

A natural join connects columns with the same name using equijoin tests. There can be multiple such columns.
Difference between inner join and natural join: An inner join specifies the columns to be joined, whereas a natural join automatically joins all columns with the same name.

SELECT A.value, B.value
FROM tablea AS A NATURAL JOIN tableb AS B;

Outer Join

An outer join preserves rows that do not have a match in the other table. It includes left outer join, right outer join, and full outer join. A left outer join preserves rows from the left table that do not have a match.
Example: Retrieve all order information for customers, including those who do not yet have orders.

SELECT Customers.cust_id, Orders.order_num
FROM Customers LEFT OUTER JOIN Orders
ON Customers.cust_id = Orders.cust_id;

Subqueries

A subquery can only return data for a single field.
The result of a subquery can be used as a filtering condition in a WHERE statement:

SELECT *
FROM mytable1
WHERE col1 IN (SELECT col2
               FROM mytable2);

Grouped Queries

  • GROUP BY Clause: Adding the GROUP BY clause after the WHERE clause groups tuples. The reserved word GROUP BY is followed by a list of grouping attributes. In the simplest case, there is only one relation following the FROM clause, and tuples are grouped based on the grouping attributes. Aggregation operators used in the SELECT clause apply to each group.
  • HAVING Clause: Assume tuples are restricted in some way before grouping, making unnecessary groups empty; appending a HAVING clause after the GROUP BY clause is sufficient. When tuples contain null values, note the following two points:
    1. Nulls are ignored in any aggregation operation.
    2. NULL values can be treated as a general value within grouping attributes.