Structured Query Language (SQL) is a popular programming language used for managing relational databases. One of the key features of SQL is the ability to combine data from multiple tables using joins. In this blog post, we will explore SQL joins and their various types.
Before diving into joins, let's first understand what a relational database is.
A relational database is a collection of tables that are related to each other. Each table contains rows (also called records) and columns (also called fields) that store information. Tables are related to each other using keys, which are columns that are used to link two or more tables.
Now, let's move on to SQL joins.
SQL Join is a clause used in Structured Query Language (SQL) to combine rows from two or more tables based on a related column between them. It's used to retrieve data that is stored across multiple tables in a relational database.
When you execute a SQL join statement, it creates a new table by merging the rows from the original tables based on a specified column or set of columns. The result of a join statement is a table that includes all columns from the joined tables.
SQL Join mainly supports four types of JOINS, and each join type defines how two tables are related in a query. The following are types of join supports in SQL Server:
Let us discuss each of these joins in detail.
This JOIN returns all records from multiple tables that satisfy the specified join condition. It is the simple and most popular form of join and assumes as a default join. If we omit the INNER keyword with the JOIN query, we will get the same output.
The SQL INNER JOIN would return the records where table1 and table2 intersect.
INNER JOIN Syntax
The following syntax illustrates the use of INNER JOIN in SQL Server:
SELECT columns FROM table1 INNER JOIN table2 ON condition1 INNER JOIN table3 ON condition2 ;
INNER JOIN Example
Let us first create two tables "Student" and "Fee" using the following statement:
CREATE TABLE Student (id int PRIMARY KEY IDENTITY, admission_no varchar(45) NOT NULL, first_name varchar(45) NOT NULL, last_name varchar(45) NOT NULL, age int, city varchar(25) NOT NULL );
CREATE TABLE Fee ( admission_no varchar(45) NOT NULL, course varchar(45) NOT NULL, amount_paid int );
Another type of join is called a LEFT OUTER JOIN. This type of join returns all rows from the LEFT-hand table specified in the ON condition and only those rows from the other table where the joined fields are equal (join condition is met).
The SQL LEFT OUTER JOIN would return the all records from table1 and only those records from table2 that intersect with table1.
Syntax
The syntax for the LEFT OUTER JOIN in SQL is:
SELECT columns FROM table1 LEFT [OUTER] JOIN table2 ON table1.column = table2.column;
Now let's look at an example that shows how to use the LEFT OUTER JOIN in a SELECT statement.
SELECT customers.customer_id, orders.order_id, orders.order_date FROM customers LEFT OUTER JOIN orders ON customers.customer_id = orders.customer_id ORDER BY customers.customer_id;
This LEFT OUTER JOIN example would return all rows from the customers table and only those rows from the orders table where the joined fields are equal.
Another type of join is called a SQL RIGHT OUTER JOIN. This type of join returns all rows from the RIGHT-hand table specified in the ON condition and only those rows from the other table where the joined fields are equal (join condition is met).
Syntax
The syntax for the RIGHT OUTER JOIN in SQL is:
SELECT columns FROM table1 RIGHT [OUTER] JOIN table2 ON table1.column = table2.column;
In some databases, the OUTER keyword is omitted and written simply as RIGHT JOIN.
Example
Now let's look at an example that shows how to use the RIGHT OUTER JOIN in a SELECT statement.
SELECT customers.customer_id, orders.order_id, orders.order_date FROM customers RIGHT OUTER JOIN orders ON customers.customer_id = orders.customer_id ORDER BY customers.customer_id ;
This RIGHT OUTER JOIN example would return all rows from the orders table and only those rows from the customers table where the joined fields are equal.
Another type of join is called a SQL FULL OUTER JOIN. This type of join returns all rows from the LEFT-hand table and RIGHT-hand table with NULL values in place where the join condition is not met.
Syntax
The syntax for the SQL FULL OUTER JOIN is:
SELECT columns FROM table1 FULL [OUTER] JOIN table2 ON table1.column = table2.column;
The SQL FULL OUTER JOIN would return the all records from both table1 and table2.
Example
Let's look at an example that shows how to use the FULL OUTER JOIN in a SELECT statement.
SELECT customers.customer_id, orders.order_id, orders.order_date FROM customers FULL OUTER JOIN orders ON customers.customer_id = orders.customer_id ORDER BY customers.customer_id ;
This FULL OUTER JOIN example would return all rows from the orders table and all rows from the customers table. Whenever the joined condition is not met, a NULL value would be extended to those fields in the result set. This means that if a customer_id value in the customers table does not exist in the orders table, all fields in the orders table will display as NULL in the result set. Also, if a customer_id value in the orders table does not exist in the customers table, all fields in the customers table will display as NULL in the result set.
SQL joins are a powerful tool for combining data from multiple tables in a relational database. Each type of join has its own specific purpose and usage, depending on the data you are trying to retrieve.
When working with SQL joins, it's important to understand the structure of your database and the relationships between tables. You should also pay close attention to the columns you are joining on, as this will determine the accuracy of your results.
SQL joins can be complex, especially when dealing with large datasets or multiple tables. However, with practice and experience, you can become proficient in using SQL joins to efficiently retrieve and combine data from multiple tables.
In addition to the four main types of joins discussed in this blog post, there are other types of joins and variations that you can use, such as self-joins and cross joins. It's important to familiarize yourself with these as well, in order to maximize the capabilities of SQL in your database management tasks.
In conclusion, SQL joins are a fundamental concept in working with relational databases, and understanding their various types is essential for anyone working with SQL. By mastering SQL joins, you can unlock the full potential of your database and retrieve the information you need in an efficient and accurate manner.
We at Alphaa AI are on a mission to tell #1billion #datastories with their unique perspective. We are the community that is creating Citizen Data Scientists, who bring in data first approach to their work, core specialisation, and the organisation.With Saurabh Moody and Preksha Kaparwan you can start your journey as a citizen data scientist.
