[21 Examples] Querying Data from a Table – An Overview of SQL Commands

Structured Query Language (SQL) is the standard language for managing and manipulating relational databases. Understanding how to query data from tables is essential for data retrieval, analysis, and reporting.

This article provides an overview of various SQL commands for querying data, including how to filter, sort, group, and join tables effectively.

Basic Queries

1. Selecting Columns
To retrieve specific columns from a table, use the `SELECT` statement:

SELECT c1, c2 FROM t;

This command fetches data from columns `c1` and `c2` in table `t`.

2. Selecting All Columns
If you need all columns from a table, you can use the wildcard `*`:

SELECT * FROM t;

This retrieves all rows and columns from table `t`.

3. Filtering Rows
To filter results based on specific conditions, the `WHERE` clause is employed:

SELECT c1, c2 FROM t WHERE condition;

This allows you to retrieve only those rows that meet the specified condition.

4. Selecting Distinct Values
To return only unique values from a column, use the `DISTINCT` keyword:

SELECT DISTINCT c1 FROM t WHERE condition;

This command fetches unique entries in `c1` that meet the given condition.

5. Sorting Results
To sort the results, the `ORDER BY` clause is used:

SELECT c1, c2 FROM t ORDER BY c1 ASC [DESC];

You can sort the results in ascending (`ASC`) or descending (`DESC`) order based on column `c1`.

6. Limiting Results
To limit the number of rows returned and skip a specified number of rows, you can use:

SELECT c1, c2 FROM t ORDER BY c1 LIMIT n OFFSET offset;

This retrieves the next `n` rows after skipping `offset` rows.

7. Grouping Data
For summarizing data, use the `GROUP BY` clause with aggregate functions:

SELECT c1, aggregate(c2) FROM t GROUP BY c1;

This groups the results based on `c1` and applies the aggregate function on `c2`.

8. Filtering Groups
To filter aggregated results, the `HAVING` clause can be added:

SELECT c1, aggregate(c2) FROM t GROUP BY c1 HAVING condition;

This allows you to specify conditions on the grouped data.

Querying from Multiple Tables

9. Inner Join
To combine rows from two tables based on a related column, use an `INNER JOIN`:

SELECT c1, c2 FROM t1 INNER JOIN t2 ON condition;

This retrieves records with matching values in both tables.

10. Left Join
To retrieve all records from the left table and the matched records from the right, use a `LEFT JOIN`:

SELECT c1, c2 FROM t1 LEFT JOIN t2 ON condition;

11. Right Join
Conversely, a `RIGHT JOIN` retrieves all records from the right table along with the matched records from the left:

SELECT c1, c2 FROM t1 RIGHT JOIN t2 ON condition;

12. Full Outer Join
For a complete view of records from both tables, regardless of matches, use a `FULL OUTER JOIN`:

SELECT c1, c2 FROM t1 FULL OUTER JOIN t2 ON condition;

13. Cross Join
To produce a Cartesian product of the two tables, use a `CROSS JOIN`:

SELECT c1, c2 FROM t1 CROSS JOIN t2;

14. Self Join
To join a table to itself, you can also use an `INNER JOIN`:

SELECT c1, c2 FROM t1 A INNER JOIN t1 B ON condition;

Using SQL Operators

15. Union
To combine results from two queries, use the `UNION` operator:

SELECT c1, c2 FROM t1 UNION [ALL] SELECT c1, c2 FROM t2;

The `ALL` option allows duplicates.

16. Intersect
To find common rows in two queries, use the `INTERSECT` operator:

SELECT c1, c2 FROM t1 INTERSECT SELECT c1, c2 FROM t2;

17. Minus
To subtract one result set from another, use the `MINUS` operator:

SELECT c1, c2 FROM t1 MINUS SELECT c1, c2 FROM t2;

18. Pattern Matching
For queries involving pattern matching, use `LIKE`:

SELECT c1, c2 FROM t WHERE c1 [NOT] LIKE pattern;

The `%` and `_` wildcards can be utilized in the pattern.

19. In List
To filter based on a list of values, use the `IN` operator:

SELECT c1, c2 FROM t WHERE c1 [NOT] IN value_list;

20. Range Queries
For fetching rows within a specified range, use the `BETWEEN` operator:

SELECT c1, c2 FROM t WHERE c1 BETWEEN low AND high;

21. Null Checks
To check for null values, use:

SELECT c1, c2 FROM t WHERE c1 IS [NOT] NULL;

Conclusion

Mastering these SQL commands is crucial for effective data manipulation and retrieval in relational databases. By understanding how to query data from tables, filter results, join tables, and use various operators, you can harness the full power of SQL for data analysis and reporting.

Whether you’re building complex queries or simple reports, these foundational techniques will serve you well in your database interactions.

You may also like: