SQL Keywords Reference

Welcome to The Coding College! This comprehensive reference guide covers SQL keywords, the building blocks of Structured Query Language. Understanding these keywords is essential for efficient database interaction.

What Are SQL Keywords?

SQL keywords are predefined, reserved words used to perform specific operations in a database. They form the syntax and structure of SQL commands. Each keyword serves a distinct purpose and cannot be used as an identifier (like table or column names).

Commonly Used SQL Keywords

Here’s a categorized reference of SQL keywords used across major database systems like MySQL, SQL Server, and PostgreSQL:

1. Data Definition Language (DDL) Keywords

These keywords define or modify the structure of database objects such as tables, schemas, and indexes.

Keyword	Description
CREATE	Creates a new database, table, or object.
ALTER	Modifies an existing database or table structure.
DROP	Deletes an existing database or table.
TRUNCATE	Deletes all records from a table, resetting it.

2. Data Manipulation Language (DML) Keywords

These keywords are used to retrieve and manipulate data stored in the database.

Keyword	Description
SELECT	Retrieves data from one or more tables.
INSERT	Adds new rows of data into a table.
UPDATE	Modifies existing data in a table.
DELETE	Removes records from a table.

3. Data Query Language (DQL) Keywords

Primarily focused on querying data.

Keyword	Description
WHERE	Filters records based on specific conditions.
ORDER BY	Sorts results in ascending or descending order.
GROUP BY	Groups rows that share the same values in specified columns.
HAVING	Filters grouped records using conditions.

4. Transaction Control Language (TCL) Keywords

These keywords control transactions within a database.

Keyword	Description
COMMIT	Saves all changes made in a transaction.
ROLLBACK	Reverts changes made in a transaction.
SAVEPOINT	Creates a point within a transaction to roll back to.

5. Control of Access Keywords

These manage database security and permissions.

Keyword	Description
GRANT	Gives specific privileges to users or roles.
REVOKE	Removes specific privileges from users or roles.

6. Join and Set Operation Keywords

These keywords manage relationships between tables or combine results of queries.

Keyword	Description
JOIN	Combines rows from two or more tables based on a related column.
INNER JOIN	Returns rows with matching values in both tables.
LEFT JOIN	Returns all rows from the left table and matching rows from the right.
RIGHT JOIN	Returns all rows from the right table and matching rows from the left.
FULL OUTER JOIN	Returns rows when there is a match in either table.
UNION	Combines the results of two queries, eliminating duplicates.
INTERSECT	Returns common rows from two queries.
EXCEPT	Returns rows from the first query not present in the second.

7. Function and Expression Keywords

Used in functions and expressions to manipulate or analyze data.

Keyword	Description
AS	Assigns an alias to a table or column.
DISTINCT	Ensures unique results by removing duplicates.
NULL	Represents missing or undefined values.
CASE	Implements conditional logic in queries.

8. Miscellaneous Keywords

Keyword	Description
EXISTS	Tests for the existence of a record in a subquery.
LIMIT	Restricts the number of rows returned by a query.
OFFSET	Specifies the starting point of the rows to return.
LIKE	Performs pattern matching with wildcard characters.
IN	Checks if a value matches any value in a list.
BETWEEN	Filters values within a specified range.

SQL Keywords by Database

Keyword	MySQL	SQL Server	MS Access
AUTO_INCREMENT	Yes	No (use IDENTITY)	No
IDENTITY	No	Yes	No
LIMIT	Yes	No (use TOP)	No

Best Practices for Using SQL Keywords

1. Capitalization: Write SQL keywords in uppercase to improve readability.
2. Avoid Reserved Words: Do not use keywords as identifiers for columns or tables.
3. Use Aliases Wisely: Use AS for column or table aliases to make queries easier to understand.
4. Combine Conditions Thoughtfully: Use AND, OR, and NOT for complex filtering.

Conclusion

SQL keywords are fundamental to writing effective queries and managing databases. Familiarizing yourself with these keywords ensures you can perform a variety of operations efficiently. Bookmark this guide for quick reference!