SQL advanced (2), SQL
SQL advanced (2) if at least one keyword of SQL INNER JOIN exists in the table, the INNER JOIN keyword returns the row.
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.
Original table (used in the example ):
"Persons" table:
| Id_P |
LastName |
FirstName |
Address |
City |
| 1 |
Adams |
John |
Oxford Street |
London |
| 2 |
Bush |
George |
Th 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 |
For inner join instances, we want to list the orders of all users.
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 if there is at least one match in the table. If the rows in "Persons" do not match in "Orders", these rows are not listed.
The SQL LEFT JOIN keyword LEFT JOIN will return all rows from the LEFT table (table_name1), even if no matching row exists 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, left join is called left outer join.
Original table (used in the example ):
"Persons" table:
| Id_P |
LastName |
FirstName |
Address |
City |
| 1 |
Adams |
John |
Oxford Street |
London |
| 2 |
Bush |
George |
Th 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 instances now, we want to list all 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 LEFT table (Persons) even if no matching row exists in the right table (Orders.
SQL RIGHT JOIN keyword
The SQL RIGHT JOIN keyword returns all rows in the RIGHT table (table_name2) even if no matching row exists 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, right join is called right outer join.
Original table (used in the example ):
"Persons" table:
| Id_P |
LastName |
FirstName |
Address |
City |
| 1 |
Adams |
John |
Oxford Street |
London |
| 2 |
Bush |
George |
Th 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 instances now, we want to list all the orders and the people who order 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 rows from the RIGHT table (Orders), even if no matching row exists in the left table (Persons.
The SQL FULL JOIN keyword returns the row if one of the tables matches.
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.
Original table (used in the example ):
"Persons" table:
| Id_P |
LastName |
FirstName |
Address |
City |
| 1 |
Adams |
John |
Oxford Street |
London |
| 2 |
Bush |
George |
Th 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 |
For full join instances, we want to list all people, their orders, all orders, and the people who order 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 right table (Orders. If the rows in "Persons" do not match in the table "Orders", or if the rows in "Orders" do not match in the table "Persons", these rows are also listed.
The SQL union and union all operators SQL UNION operators are used to merge the result sets of two or more SELECT statements.
Note that the SELECT statement inside the UNION must have the same number of columns. Columns must also have similar data types. In addition, the columns in each SELECT statement must be in the same order.
SQL UNION syntax
SELECT column_name(s) FROM table_name1UNIONSELECT column_name(s) FROM table_name2
Note: by default, the UNION operator selects different values. If repeated 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 the same as the column name in the first SELECT statement in the UNION.
The original table 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 to list all different employee names in China and the United States:
SELECT E_Name FROM Employees_ChinaUNIONSELECT E_Name FROM Employees_USA
Result
| 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 with the same name. Only one of them is listed. The UNION command only selects different values.
The 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 all employees in China and the United States:
SELECT E_Name FROM Employees_ChinaUNION ALLSELECT E_Name FROM Employees_USA
Result
| E_Name |
| Zhang, Hua |
| Wang, Wei |
| Carter, Thomas |
| Yang, Ming |
| Adams, John |
| Bush, George |
| Carter, Thomas |
| Gates, Bill |
SQL advanced assignment query Summary
Insert into Table B
SELECT a1, SUM (a4) FROM Table a group by a1
SQL advanced questions
Declare @ SQL varchar (8000)
Select @ SQL = 'select id, NAME, DAPTNAME ,'
Select @ SQL = @ SQL + 'sum (case date when ''' + date + ''' then abs else 0 end) as '+ date + ', 'From (select distinct date from Table B)
Select @ SQL = substring (@ SQL, 1, len (@ SQL)-1) + 'into implement menu 3 from Table a, Table B where Table. id = table B. id group by id, NAME, DAPTNAME'
Exec (@ SQL)
---
The above is helpful to you.
