Reading time: 4 minutes
SQL syntax refers to the structure of statements used to interact with a database. It’s important to be familiar with the correct SQL syntax as each statement has a specific format, and any syntax errors can cause the statement to not function properly.
A typical SQL statement consists of three parts: the SELECT clause, the FROM clause, and the WHERE clause. The SELECT clause specifies which fields we want to retrieve from the database. For example, if we want to retrieve the name and salary of all employees, we could use the following statement:
SELECT name, salary FROM employees
The FROM clause specifies from which table we want to retrieve the data. In the above example, we are retrieving the data from the “employees” table.
The WHERE clause allows us to filter the query results and only display the records that meet certain conditions. For example, if we want to only display employees whose salary is above a certain threshold (e.g., 50000), we could use the following statement:
SELECT name, salary FROM employees WHERE salary > 50000
In addition to these three clauses, SQL has a set of operators that allow us to perform more complex operations on the data. Some examples of these operators are:
- AND: allows combining multiple conditions into a single WHERE clause. For example, we could use the following statement to only display employees whose salary is above 50000 and whose department is the marketing department:
SELECT name, salary FROM employees WHERE salary > 50000 AND department = 'marketing'
- OR: allows including multiple options in a single WHERE clause. For example, we could use the following statement to display all employees whose salary is above 50000 or whose department is the marketing department:
SELECT name, salary FROM employees WHERE salary > 50000 OR department = 'marketing'
- IN: allows specifying multiple options for a specific field. For example, we could use the following statement to display all employees whose department is either the marketing department or the sales department:
SELECT name, salary
FROM employees
WHERE department IN ('marketing', 'sales')
Another important feature of SQL is the ability to join tables from different databases or even different servers. This is done using the JOIN clause. There are different types of joins, such as INNER JOIN, OUTER JOIN (LEFT JOIN, and RIGHT JOIN), each allowing us to combine data from different tables in a different way.
INNER JOIN is a join operation that allows us to combine the data from two tables in such a way that only the records with a match in both tables are included. For example, if we have an “employees” table and a “departments” table, and we want to display the name and salary of all employees along with the name of their department, we could use the following statement:
SELECT employees.name, employees.salary, departments.name FROM employees INNER JOIN departments ON employees.department_id = departments.id
This statement combines the data from the “employees” table with the data from the “departments” table using the “department_id” field as the key. Only the records with a match in both tables are included, i.e., only the employees who have a department assigned in the “departments” table.
OUTER JOIN is a join operation that allows us to combine the data from two tables in such a way that all the records from both tables are included, even if they don’t have a match in the other table. There are two types of OUTER JOIN: LEFT JOIN and RIGHT JOIN.
- LEFT JOIN is a join operation that includes all the records from the left table (the first table in the statement) and only the matching records from the right table (the second table in the statement). For example, if we want to display the name and salary of all employees along with the name of their department, even if some employees don’t have a department assigned, we could use the following statement:
SELECT employees.name, employees.salary, departments.name FROM employees LEFT JOIN departments ON employees.department_id = departments.id
In this case, all records from the “employees” table are included, even if some don’t have a department assigned in the “departments” table. In these cases, the “departments.name” field will have a NULL value.
- RIGHT JOIN: is a join operation that includes all the records from the right table (the second table in the statement) and only the matching records from the left table (the first table in the statement). For example, if we want to display the name and salary of all employees along with the name of their department, even if some departments don’t have employees assigned, we could use the following statement:
SELECT employees.name, employees.salary, departments.name FROM employees RIGHT JOIN departments ON employees.department_id = departments.id
In this case, all records from the “departments” table are included, even if some don’t have employees assigned in the “employees” table. In these cases, the “employees.name” and “employees.salary” fields will have a NULL value.
I hope this helps, see you in the next one :four_leaf_clover:
