Joining two database tables on a single query is one of the most useful tools available on SQL. To understand the Joins and Unions clauses let’s first review some SQL terminology.
Statements: In SQL, a statement is a text string that database engines can understand and process.
Clauses: Specific tasks performed by the SQL. SELECT, CREATE, UPDATE, DELETE are examples of SQL clauses.
Parameters: The list of columns, data types or values that are passed to a clause as an argument
CREATE TABLE users (
id INT,
name TEXT
);In the example above, CREATE TABLE is a clause and (id INT, name TEXT) are parameters. This forms a SQL statement that can be interpreted by a database engine.
Join is an SQL clause that allows to combine records from two or more tables. It does so by combining fields that are common to the joined tables. The Join clause is performed on the WHERE clause.
A set of operators are available when using the Join clause: =, <, >, <>, <=, >=, !=, BETWEEN, LIKE, NOT. The equal symbol is the generally used as a join operator.
There are multiple types Join operations that can be performed.
Here are examples of SQL Join using the following database structure. There are 2 tables: student and course. We are going to see how to join these two tables.
The INNER Join is used to obtain the shared values between two or more tables. These values refer to the common records of the tables.
As an example, we are going to query the database to join the student and the course table so that we can get the list of students in related to their current course.
The SQL query to INNER Join two tables is the following:
SELECT student.name as student, course.name as course
FROM student INNER JOIN course
ON student.course = course.id;
The LEFT Join and RIGHT Join are used to get the common values from the joined tables while keeping the non-matched records from the table of origin. The table of origin can be controlled by either setting the LEFT or RIGHT Join.
For example, performing a LEFT Join statement on our student and course tables is going to return the full list of students. Even if a student does not have a course it will still be returned by the SQL, which will set the course field to NULL. Example of LEFT Join in SQL:
SELECT student.name as student, course.name as course
FROM student LEFT JOIN course
ON student.course = course.id;
In the case of RIGHT Join the SQL statement is quite similar. The main difference is that the returning values will now contain all records from the right table. Even if a course is not being accessed by any student, SQL will return the course with the student field set to NULL.
SELECT student.name as student, course.name as course
FROM student RIGHT JOIN course
ON student.course = course.id;
A FULL Join is meant for retrieving records from two or more tables when there is a single match. This operation can retrieve a large amount of data set so it needs to be performed carefully.
SELECT student.name as student, course.name as course
FROM student FULL OUTER JOIN course
ON student.course = course.id;There is no FULL Join on MYSQL. In order to perform a FULL Join in MYSQL you can use the UNION ALL clause. Example:
SELECT *
FROM student LEFT JOIN course ON student.course=course.id
UNION ALL
SELECT *
FROM student RIGHT JOIN course ON student.course=course.id;
SQL Joins can also be used to get the non-matching records of two database tables. For example, students that are not enrolled in a course or courses that do not have any students. In order to perform subtraction in SQL Joins, you should use the WHERE clause.
Example of getting all students that are not enrolled in any course.
SELECT student.name as student
FROM student LEFT JOIN course
ON student.course = course.id
WHERE course.name IS NULL;Example of getting all courses that do not have any student.
SELECT course.name
FROM course LEFT JOIN student
ON student.course=course.id
WHERE student.name IS NULL;In SQL the Group By clause is used to a set of records using a specific column. This is very helpful when we want to query a database using a common aggregate function such as: COUNT, MAX, MIN, SUM, AVG.
As an example, consider the following database structure with two tables: employee and department.
Using an SQL Join in combination with Group By we can select the maximum salary of employee per each department. To do so we are going to use the SQL Right Join and the aggregate function MAX. Example:
SELECT department.name, MAX(employee.salary) as salary
FROM employee RIGHT JOIN department
ON employee.department = department.id
GROUP BY department.name;The SQL Union clause can be used to combine records from multiple SELECT clauses. There are few conditions that are required for using union:
Example of SQL Union to get all names from employee and student tables:
SELECT name FROM employee
UNION ALL
SELECT name FROM student
ORDER BY name ASC;Note: The UNION operator selects only distinct values, meaning there won’t be any duplications. In order to get duplicated values we should use UNION ALL.
Data modeling is the practice of translating an information system into models in order to represent its internal structure. It often involves the use of diagrams and schemas to represent data flow across a software application.
The twelve-factor app is a methodology used to build software-as-a-service apps that are easy to scale up without significant changes to tooling or architecture.
Quick introduction to Kotlin programming language: what is Kotlin used for and which are the benefits of developing in Kotlin. This article covers the basic use cases for Kotlin and why it is so popular.