YouTip

* * * SQL join is used to combine rows from two or more tables. The following diagram shows 7 usages related to LEFT JOIN, RIGHT JOIN, INNER JOIN, OUTER JOIN. [!( | Type | Description | | --- | --- | | **INNER JOIN** | Returns records that have matching values in both tables (intersection). | | **LEFT JOIN** | Returns all records from the left table, even if there are no matches in the right table (preserves left table). | | **RIGHT JOIN** | Returns all records from the right table, even if there are no matches in the left table (preserves right table). | | **FULL OUTER JOIN** | Returns the union of both tables, including matching and non-matching records. | | **CROSS JOIN** | Returns the Cartesian product of the two tables, each left table row combined with each right table row. | | **SELF JOIN** | Joins a table with itself. | | **NATURAL JOIN** | Automatically joins tables based on columns with the same name. | ### Example Data **Table 1: Customers** | CustomerID | Name | | --- | --- | | 1 | Alice | | 2 | Bob | | 3 | Charlie | **Table 2: Orders** | OrderID | CustomerID | Product | | --- | --- | --- | | 101 | 1 | Laptop | | 102 | 2 | Phone | | 103 | 4 | Tablet | #### Comparison of Join Results | Join Type | Result | | --- | --- | | **INNER JOIN** | Returns matching records from both tables. In the given example, only records with CustomerID 1 and 2 have matches in both tables, so only these records will be returned. | | **LEFT JOIN** | Returns all records from the left table (Customers), even if there are no matches in the right table (Orders). For left table records that have no match in the right table, the right table fields in the result will be NULL. In the example, the record with CustomerID 3 has no match in the right table, so its corresponding Product will be NULL. | | **RIGHT JOIN** | Returns all records from the right table (Orders), even if there are no matches in the left table (Customers). For right table records that have no match in the left table, the left table fields in the result will be NULL. In the example, the record with OrderID 103 has no match in the left table, so its corresponding Name will be NULL. | | **FULL OUTER JOIN** | Returns all records from both tables, regardless of whether they match. If a table has no matching record, its fields will be NULL. In the example, the records with CustomerID 3 and OrderID 103 will appear as NULL in the opposite table. | | **CROSS JOIN** | Returns the Cartesian product of the two tables, i.e., each row from the left table combined with each row from the right table. In the example, each customer will be combined with each order, producing multiple results. | | **SELF JOIN** | A table joins with itself. This is typically used to query records that are related within the table, such as the relationship between employees and their managers. | * * * ## SQL JOIN The SQL JOIN clause is used to combine rows from two or more tables, based on the common fields between these tables. The most common JOIN type is **SQL INNER JOIN (simple JOIN)**. SQL INNER JOIN returns all rows that satisfy the JOIN condition from multiple tables. ### Syntax: SELECT column1, column2, ... FROM table1 JOIN table2 ON condition; **Parameter Description:** * **column1, column2, ...**: The field names to select, can be multiple fields. If no field names are specified, all fields will be selected. * **table1**: The first table to join. * **table2**: The second table to join. * **condition**: The join condition, used to specify how to join. * * * ## Demo Database In this tutorial, we will use the sample database. Below is data selected from the 'Websites' table: +----+--------------+---------------------------+-------+---------+| id | name | url | alexa | country |+----+--------------+---------------------------+-------+---------+| 1 | Google | https://www.google.cm/ | 1 | USA || 2 | Taobao | https://www.taobao.com/ | 13 | CN || 3 | | | 4689 | CN || 4 | Weibo | http://weibo.com/ | 20 | CN || 5 | Facebook | https://www.facebook.com/ | 3 | USA || 7 | stackoverflow | http://stackoverflow.com/ | 0 | IND |+----+---------------+---------------------------+-------+---------+ Below is the data from the 'access_log' website access log table: mysql> SELECT * FROM access_log;+-----+---------+-------+------------+| aid | site_id | count | date |+-----+---------+-------+------------+| 1 | 1 | 45 | 2016-05-10 || 2 | 3 | 100 | 2016-05-13 || 3 | 1 | 230 | 2016-05-14 || 4 | 2 | 10 | 2016-05-14 || 5 | 5 | 205 | 2016-05-14 || 6 | 4 | 13 | 2016-05-15 || 7 | 3 | 220 | 2016-05-15 || 8 | 5 | 545 | 2016-05-16 || 9 | 3 | 201 | 2016-05-17 |+-----+---------+-------+------------+9 rows in set (0.00 sec) Please note, the '**id**' column in the 'Websites' table points to the '**site_id**' field in the 'access_log' table. These two tables are related via the 'site_id' column. Then, if we run the following SQL statement (containing INNER JOIN): ## Example SELECT Websites.id, Websites.name, access_log.count, access_log.date FROM Websites INNER JOIN access_log ON Websites.id=access_log.site_id; Executing the above SQL produces the following output: !( * * * ## Different SQL JOIN Before we continue with the examples, let's list the different SQL JOIN types you can use: * **INNER JOIN**: Returns rows if there is at least one match in the tables * **LEFT JOIN**: Returns all rows from the left table, even if there are no matches in the right table * **RIGHT JOIN**: Returns all rows from the right table, even if there are no matches in the left table * **FULL JOIN**: Returns rows if there is a match in one of the tables * * *