Buzz
The JOINS condition in SQL is employed in SELECT, UPDATE, DELETE statements with WHERE clause in SQL.
SQL JOINS Command
Syntax of SQL JOINS command for linking 2 tables:
SELECT col1, col2, col3...
FROM table_name1 INNER JOIN table_name2
ON table_name1.col2 = table_name2.col1;
If the JOINS condition in SQL is omitted or invalid, it will return the Cartesian product of records from 2 or more connected tables. The Cartesian product returns the number of rows equivalent to the product of all rows in the connected tables.
For example, if the first table has 20 rows and the second table has 10 rows, the result returned will be 20 * 10, equivalent to 200 rows. This query takes a long time to execute.
An example of SQL JOINS command
Use the JOINS condition in SQL to connect the following 2 tables:
Database table Product:
Database table order_items:
JOINS conditions in SQL are divided into 2 types Equi join and Non Equi join.
SQL Equi join
The condition of the JOINS command in SQL uses the equal sign (=) as the comparison operator. Equi join is divided into 2 types: SQL Outer join and SQL Inner join.
For example: you can retrieve information about customers who bought products and the quantity of products.
SQL Non equi join
This condition uses different comparison operators, such as >, <, >=, <>
1. Exploring SQL Equi Join
Equi-join is divided into 2 types:
- SQL Inner Join
- SQL Outer Join
1.1. Understanding SQL Inner Join
All rows returned by the SQL query satisfy the specified SQL join condition.
Example of SQL Inner Join:
If you want to display product information for each order, the query will look like the following. Since you are retrieving data from 2 tables, you need to specify the common column between the 2 tables, which is product_id.
The SQL query is structured as follows:
SELECT order_id, product_name, unit_price, supplier_name, total_units
FROM product, order_items
WHERE order_items.product_id = product.product_id;
Columns must be referenced by table name in the Join condition, as product_id is a column in both tables and needs to be specified in a way. This is to avoid using columns in the SELECT statement in SQL.
The number of Join conditions is (n-1), if there are more than 2 connected tables in a query, where n is the number of related tables. This rule must be followed to avoid Cartesian products.
Additionally, you can use aliases to reference column names, the query will look like this:
SELECT o.order_id, p.product_name, p.unit_price, p.supplier_name, o.total_units
FROM order_items o, product p
WHERE o.product_id (+) = p.product_id;
1.2. SQL Outer Join
The Join condition returns all rows from both tables that satisfy the join condition along with rows that do not satisfy the condition from either of the tables. The SQL Outer Join operator in Oracle is (+) and is only used on one side of the Join condition.
Here's an example of a SQL Self Join query:
SELECT a.sales_person_id, a.name AS employee_name, a.manager_id, b.sales_person_id AS manager_id, b.name AS manager_name
FROM sales_person AS a JOIN sales_person AS b
ON a.manager_id = b.sales_person_id;
2. Understanding SQL Non-Equi Join
Non-Equi Join is a SQL JOIN condition established using all comparison operators except for equals (=). For example, >=, <=, >, <.
Example of SQL Non-Equi Join:
Suppose you want to find the names of students who are not enrolled in the economics course, the SQL query would look like the following (using the student information table introduced in previous articles):
Fetch First Name, Last Name, and Subject
FROM student_details
WHERE subject <> 'Economics'
The output will be as follows:
Here are detailed information and examples of SQL JOINS. Essentially, SQL JOINS are used to connect data across different tables. Additionally, you can explore SQL UPDATE statements here. If you have any questions or need clarification, please feel free to leave a comment below the article.
