Structured Query Language (SQL) is a strong instrument that can be used to handle and modify relational databases. The join is one of the chief operations in SQL that helps you combine rows from two or more tables based on a related column. In this article, we will examine various kinds of joins involving inner, left, right and full joins by using the specified keywords.
Table of Contents
DIFFERENT TYPES OF SQL JOINS
In SQL there are different types of joins having a unique purpose:
- INNER JOIN
- LEFT JOIN( Left outer join)
- RIGHT JOIN( Right outer join)
- FULL JOIN( Full outer join)
- Natural join
INNER JOIN
Inner join returns the records that have matching values in both the tables. It is the most common join in SQL and is used when you need to retrieve the rows that have corresponding values in both tables.
SYNTAX:
SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;
EXAMPLE:
SELECT employees.name, departments.department_name
FROM employees
INNER JOIN departments
ON employees.department_id = departments.department_id;
EXPLANATION: In this example, we are retrieving the names of employees and the names of their departments. The INNER JOIN ensures that only employees who are assigned to a department are included in the results. The join key is the department_id, which must match in both the employees and departments tables for a row to be included in the result set.
LEFT JOIN
Left join returns all the records from the left table or table1, and the matched records from the right table table2. If there is no match, the result is null from the right side.
SYNTAX:
SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;
EXAMPLE:
SELECT employees.name, departments.department_name
FROM employees
LEFT JOIN departments
ON employees.department_id = departments.department_id;
EXPLANATION: This query retrieves the names of all employees and their corresponding department names. If an employee is not assigned to any department, the department_name will be NULL in the result set. The LEFT JOIN is useful when you need to include all records from the left table, regardless of whether there is a match in the right table.
RIGHT JOIN
Right join is similar to left join , it returns all the records from the right table or table2 and only matched record from the left table or table1. If there is no match, the result is null from the left side.
SYNTAX:
SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;
EXAMPLE:
SELECT employees.name, departments.department_name
FROM employees
RIGHT JOIN departments
ON employees.department_id = departments.department_id;
EXPLANATION: In this query, we retrieve the names of employees and their departments. If a department does not have any employees assigned to it, the name column will be NULL in the result set. The RIGHT JOIN is useful when you need to include all records from the right table, even if there are no corresponding records in the left table.
FULL JOIN
Full join returns all the records even when there are no matches between the left table or table1 and the right table or the table2. If there is no matches the result is null from the side where there is no match.
SYNTAX:
SELECT column_name(s)
FROM table1
FULL JOIN table2
ON table1.column_name = table2.column_name;
EXAMPLE:
SELECT employees.name, departments.department_name
FROM employees
FULL JOIN departments
ON employees.department_id = departments.department_id;
EXPLAINATION: This query retrieves all employee names and department names, including those records that do not have a match in the other table. If an employee is not assigned to a department, or if a department has no employees, the respective columns will contain NULL values. The FULL JOIN is useful for combining all records from both tables, ensuring no data is omitted.
KEY POINTS AND USAGE
- SQL join key: The column(s) used to join tables. For example, department_id in the examples above.
- ON in SQL: Clause used to specify the condition to join tables. It defines the relationship between columns in different tables.
- What is full join: A join that returns all records from both tables, with NULLs in places where the join condition is not met.
- As of join: Not a standard SQL term; possibly refers to specific temporal joins in some databases, used to join tables based on time or versioning.
- Join type: Refers to the type of join operation (inner, left, right, full, natural). Each type serves different data retrieval needs.
- What is right join: A join that returns all records from the right table and matched records from the left table.
What are Layer 1 and Layer 2 Tokens on Binance?
Binance is one of the leading cryptocurrency exchanges in the world, offering a wide range of trading options and services for its users. One feature that makes Binance stand out is its support for layer 1 and layer 2 tokens.…
Tealstreet.io Review-Best Trading Terminal?
If you are looking for a new and exciting way to trade crypto derivatives, you might want to check out Tealstreet.io, a web-based trading terminal with advanced features and security. In this article, We will review Tealstreet.io based on its…
XM Review: Forex and CFD Broker
Founded in 2009, XM is a well-known Forex and CFD broker. The company’s parent organization, Trading Point of Financial Instruments Ltd., is headquartered in Cyprus.XM focuses on delivering online trading solutions and specializes in a variety of financial products such as…
MoonBag Soars: Top Crypto Presale in June 2024 Boasts High ROI and Referral Rewards
The cryptocurrency market is a dynamic landscape, constantly churning out new projects vying for investor attention. Analysts at Best Crypto Presales believe that despite the saturation, there are many exciting upcoming projects for investors to consider, including Pepe Unchained, WienerAI,…
Hodlerhacks Balance Bot Review: Is It a Good Shrimpy Alternative?
This article reviews the Hodlerhacks Balance Bot, a free and open-source crypto trading bot that automates the popular crypto portfolio rebalancing strategy. Summary Balance Bot automates a portfolio balancing/ rebalancing strategy. It is available free of charge, with no strings…
CoinDCX Expands, Acquires Dubai-based BitOasis
Key Takeaways India-based crypto exchange, CoinDCX, has acquired Dubai-based digital asset platform BitOasis, which mainly operates in the Middle East and North Africa (MENA) region. “Combined with the company’s newly acquired licence in the Kingdom of Bahrain and the recent…
Top 6 Bitsgap Alternatives
Bitsgap is one of the best crypto trading bots providers. However, while choosing a service, you must know about the alternate options you have. Hence, this article will cover the five best Bitsgap alternatives. Summary (TL;DR) The Bitsgap platform allows…
Pluto Bio vs. SciNote – Which one you should choose?
For the past few years, the field of Biology has been undergoing a world-changing transformation. The revolutionary methods of RNA-seq and ChIP-seq are rapidly changing the definition of research. These methods provide significant data as well as challenging output. Bioinformatics…