Typically, the JOINS command in SQL is used to join information in different tables, the JOINS condition is part of the SQL query to retrieve rows from 2 or more tables.
The JOINS statement condition in SQL is used in the SELECT, UPDATE, DELETE statements with WHERE clause in SQL.
JOINS command in SQL
Syntax of JOINS command in SQL to link 2 tables:
SELECT col1, col2, col3 …
FROM table_name1, table_name2
WHERE table_name1.col2 = table_name2.col1;
If the JOINS condition in SQL is omitted or otherwise valid, it will return the Cartesian product of the record set from 2 or more join tables. The Cartesian product returns the same number of rows as all the rows in the linked tables.
For example, if the first table has 20 rows and the second table has 10 rows, the result will be 20 * 10, equivalent to 200 rows. This query takes a long time to execute.
Example of JOINS statement in SQL
Use the JOINS condition in SQL to connect the following 2 tables:
Database table Product:
Database table order_items:
The JOINS condition in SQL is classified into two categories Equi join and Non Equi join.
SQL Equi join
The condition of the JOINS statement in this 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 get customer information about product purchases and product quantities.
SQL Non equi join
This condition uses other comparison operators, such as>, =,
1. Learn SQL Equi Join
An equi-join is classified into two categories:
– SQL Inner Join
– SQL Outer Join
1.1. SQL Inner Join
All rows returned by the SQL query satisfy the specified SQL connection condition.
SQL Inner Join example:
If you want to display product information for each order, the query will look like the following. Because you are retrieving data from two tables, the common column between product_id is defined.
The SQL query looks like this:
SELECT order_id, product_name, unit_price, supplier_name, total_units
FROM product, order_items
WHERE order_items.product_id = product.product_id;
The columns must be referenced by the table name in the Join condition, because the product_id is a column in both tables and needs to be specified in one way. This is to avoid using columns in the SELECT statement in SQL.
The number of the Join condition is (n-1), if there are more than 2 join tables in a query, where n is the number of related tables. The rule must be true to avoid Cartesian products.
In addition, you can also use aliases to reference column names, the query will look like the following:
SELECT o.order_id, p.product_name, p.unit_price, p.supplier_name, o.total_units
FROM product p, order_items o
WHERE o.product_id = p.product_id;
1.2. SQL Outer Join
The Join condition returns all rows from both tables that meet the join conditions and those that do not meet the condition from one of the tables. The SQL Outer Join operator in Oracle is (+) and is only used on one side of the Join condition.
Different RDBMS deployment syntaxes are different. Very few of these syntaxes represent Join conditions like “sql left outer join”, “sql right outer join”.
If you want to display all the data in the product table and the data in the order items table, with null values displayed for orders if a product is not available, the SQL Outer Join query looks like this: here:
SELECT p.product_id, p.product_name, o.order_id, o.total_units
FROM order_items o, product p
WHERE o.product_id (+) = p.product_id;
Note: If the operator (+) is used on the left of the Join condition, it is equivalent to the SQL left outer join. If used on the right hand side the Join condition is equivalent to SQL right outer join.
SQL Self Join
SQL Self Join is a type of JOIN in SQL, used to connect a separate table, especially in cases where the table has FOREIGN KEY referencing PRIMARY KEY. Make sure the JOIN command identifies aliases for both copies of the table.
The following example is a SQL Self Join query:
SELECT a.sales_person_id, a.name, a.manager_id, b.sales_person_id, b.name
FROM sales_person a, sales_person b
WHERE a.manager_id = b.sales_person_id;
2. SQL Non Equi Join
Non Equi Join is a conditional JOIN statement that is set using all comparison operators, except the equal operator (=). Such as> =, .
SQL Non Equi Join example:
If you want to find the names of students who are not enrolled in economics courses, the SQL query will look like the following (using the student information table that Taimienphi.vn introduced in previous articles):
SELECT first_name, last_name, subject
WHERE subject! = ‘Economics’
The output will look like this:
Above are the details and examples of JOINS command in SQL. Basically the JOINS command in SQL is used to concatenate information in different tables. Also, you can learn more. UPDATE statement in SQL here. If you have any questions or questions, please leave your comments in the comment section below the article.