The INNER join keyword returns rows when the SQL Advanced (2) SQL INNER join keyword has at least one match in the table.
INNER JOIN keyword Syntax
SELECT column_name (s) from Table_name1inner JOIN table_name2 on Table_name1.column_name=table_name2.column_name
Note: INNER join is the same as join.
The original table (used in the example):
"Persons" table:
id_p |
LastName |
FirstName |
Address |
| City
1 |
Adams |
John |
Oxford Street |
London |
2 |
Bush |
George |
Fifth Avenue |
New York |
3 |
Carter |
Thomas |
Changan Street |
Beijing |
"Orders" table:
Id_o |
OrderNo |
id_p |
1 |
77895 |
3 |
2 |
44678 |
3 |
3 |
22456 |
1 |
4 |
24562 |
1 |
5 |
34764 |
65 |
INNER JOIN (INNER join) instance now we want to list everyone's orders.
You can use the following SELECT statement:
SELECT Persons.lastname, Persons.firstname, Orders.ordernofrom personsinner JOIN Orderson persons.id_p=orders.id_ Porder by Persons.lastname
Result set:
LastName |
FirstName |
OrderNo |
Adams |
John |
22456 |
Adams |
John |
24562 |
Carter |
Thomas |
77895 |
Carter |
Thomas |
44678 |
The INNER JOIN keyword returns a row when there is at least one match in the table. If the rows in "Persons" do not match in "Orders," the rows are not listed.
The SQL LEFT JOIN keyword, the table_name1, is returned for all rows, even if there are no matching rows in the right table (table_name2).
Left JOIN keyword syntax
SELECT column_name (s) from Table_name1left JOIN table_name2 on Table_name1.column_name=table_name2.column_name
Note: In some databases, the left join is called the left OUTER join.
The original table (used in the example):
"Persons" table:
id_p |
LastName |
FirstName |
Address |
| City
1 |
Adams |
John |
Oxford Street |
London |
2 |
Bush |
George |
Fifth Avenue |
New York |
3 |
Carter |
Thomas |
Changan Street |
Beijing |
"Orders" table:
Id_o |
OrderNo |
id_p |
1 |
77895 |
3 |
2 |
44678 |
3 |
3 |
22456 |
1 |
4 |
24562 |
1 |
5 |
34764 |
65 |
Left join (left JOIN) instance now, we want to list all the people and their orders-if any.
You can use the following SELECT statement:
SELECT Persons.lastname, Persons.firstname, Orders.ordernofrom personsleft JOIN Orderson persons.id_p=orders.id_ Porder by Persons.lastname
Result set:
LastName |
FirstName |
OrderNo |
Adams |
John |
22456 |
Adams |
John |
24562 |
Carter |
Thomas |
77895 |
Carter |
Thomas |
44678 |
Bush |
George |
|
The left JOIN keyword returns all rows from the table (Persons), even if there are no matching rows in the right table (Orders).
SQL Right JOIN keyword
The right join keyword in the "r" keyword will return all rows in the table (table_name2), even if there are no matching rows in the left table (table_name1).
Right JOIN keyword syntax
SELECT column_name (s) from Table_name1right JOIN table_name2 on Table_name1.column_name=table_name2.column_name
Note: In some databases, the right join is called a OUTER join.
The original table (used in the example):
"Persons" table:
id_p |
LastName |
FirstName |
Address |
| City
1 |
Adams |
John |
Oxford Street |
London |
2 |
Bush |
George |
Fifth Avenue |
New York |
3 |
Carter |
Thomas |
Changan Street |
Beijing |
"Orders" table:
Id_o |
OrderNo |
id_p |
1 |
77895 |
3 |
2 |
44678 |
3 |
3 |
22456 |
1 |
4 |
24562 |
1 |
5 |
34764 |
65 |
Right Join instance now we want to list all the orders and the people who ordered them-if any.
You can use the following SELECT statement:
SELECT Persons.lastname, Persons.firstname, Orders.ordernofrom personsright JOIN Orderson persons.id_p=orders.id_ Porder by Persons.lastname
Result set:
LastName |
FirstName |
OrderNo |
Adams |
John |
22456 |
Adams |
John |
24562 |
Carter |
Thomas |
77895 |
Carter |
Thomas |
44678 |
|
|
34764 |
The right JOIN keyword returns all of the rows from the table (Orders), even if there are no matching rows in the left table (Persons).
SQL full join keyword SQL full join keyword The full join keyword returns a row as long as there is a match in one of the tables.
Full JOIN keyword syntax
SELECT column_name (s) from Table_name1full JOIN table_name2 on Table_name1.column_name=table_name2.column_name
Note: In some databases, full join is called full OUTER join.
The original table (used in the example):
"Persons" table:
id_p |
LastName |
FirstName |
Address |
| City
1 |
Adams |
John |
Oxford Street |
London |
2 |
Bush |
George |
Fifth Avenue |
New York |
3 |
Carter |
Thomas |
Changan Street |
Beijing |
"Orders" table:
Id_o |
OrderNo |
id_p |
1 |
77895 |
3 |
2 |
44678 |
3 |
3 |
22456 |
1 |
4 |
24562 |
1 |
5 |
34764 |
65 |
Full join instances now we want to list all the people, their orders, all the orders, and the people who ordered them.
You can use the following SELECT statement:
SELECT Persons.lastname, Persons.firstname, Orders.ordernofrom personsfull JOIN Orderson persons.id_p=orders.id_ Porder by Persons.lastname
Result set:
LastName |
FirstName |
OrderNo |
Adams |
John |
22456 |
Adams |
John |
24562 |
Carter |
Thomas |
77895 |
Carter |
Thomas |
44678 |
Bush |
George |
|
|
|
34764 |
The full JOIN keyword returns all rows from the left table (Persons) and the right table (Orders). If the rows in "Persons" do not match in the table "orders", or if the rows in "orders" do not have a match in the table "Persons", the rows are also listed.
The SQL union and UNION ALL operator SQL UNION operator UNION operator is used to combine the result set of two or more SELECT statements.
Note that the SELECT statement inside the UNION must have the same number of columns. The column must also have a similar data type. Also, the order of the columns in each SELECT statement must be the same.
SQL UNION Syntax
SELECT column_name (s) from Table_name1unionselect column_name (s) from table_name2
Note: By default, the UNION operator chooses a different value. If duplicate values are allowed, use UNION all.
SQL UNION All syntax
SELECT column_name (s) from Table_name1union Allselect column_name (s) from table_name2
In addition, the column name in the union result set is always equal to the column name in the first SELECT statement in the Union.
The original table is used in the following example:
Employees_china:
e_id |
E_name |
01 |
Zhang, Hua |
02 |
Wang, Wei |
03 |
Carter, Thomas. |
04 |
Yang, Ming |
Employees_usa:
e_id |
E_name |
01 |
Adams, John. |
02 |
Bush, George. |
03 |
Carter, Thomas. |
04 |
Gates, Bill. |
Use the UNION command instance to list all the different employee names in China and the United States:
SELECT E_name from Employees_chinaunionselect e_name from Employees_usa
Results
E_name |
Zhang, Hua |
Wang, Wei |
Carter, Thomas. |
Yang, Ming |
Adams, John. |
Bush, George. |
Gates, Bill. |
Note: This command cannot list all employees in China and the United States. In the above example, we have two employees of the same name, and only one of them is listed. The UNION command will only pick a different value.
The UNION ALL UNION ALL command is almost equivalent to the Union command, but the union ALL command lists all values.
SQL Statement 1UNION allsql Statement 2
Use the UNION all command
Example: List of all employees in China and the United States:
SELECT e_name from Employees_chinaunion allselect e_name from Employees_usa
Results
E_name |
Zhang, Hua |
Wang, Wei |
Carter, Thomas. |
Yang, Ming |
Adams, John. |
Bush, George. |
Carter, Thomas. |
Gates, Bill. |
SQL Advanced (2)