SQL Advanced (ii) (alias) SQL Join Union and UNION ALL)

Alias (aliases)

eg (using table name aliases):

Select Po.orderid,p.lastname,p.firstname from persons as p,product_orders as Po where p.lastname= ' Adams ' and p.firstname= ' John ' (using aliases)

Select Product_orders.orderid,persons.lastname,persons.firstname from Persons,product_orders where persons.lastname = ' Adams ' and Persons.firstname= ' John ' (Do not use aliases)

eg (using a column name alias):

Select LastName as family,firstname as name from persons

SQL join is used to query data from these tables based on the relationship between the columns in two or more tables.

Refer to two tables:

SELECT Persons.lastname, Persons.firstname, Orders.orderno from Persons, Orders WHERE Persons.id_p = orders.id_p

SQL Join-Using join

SELECT Persons.lastname, Persons.firstname, Orders.orderno from Persons INNER JOIN Orders

On persons.id_p = Orders.id_p ORDER by persons.lastname

Inner JOIN is the same as join. The INNER JOIN keyword returns a row when there is at least one match in the table.

The left JOIN keyword returns all rows from the table (TABLE_NAME1), even if there are no matching rows in the right table (table_name2).

Select Persons.lastname, Persons.firstname, Orders.orderno from Persons left JOIN Orders on

Persons.id_p=orders.id_p ORDER by Persons.lastname

The right JOIN keyword returns all rows from the table (table_name2), even if there are no matching rows in the left table (table_name1).

SELECT persons.lastname, Persons.firstname, Orders.orderno from Persons right JOIN Orders on

Persons.id_p=orders.id_p ORDER by Persons.lastname

Full JOIN keyword: The full join keyword returns a row whenever there is a match in one of the tables.

SELECT Persons.lastname, Persons.firstname, Orders.orderno from Persons full JOIN Orders on

Persons.id_p=orders.id_p ORDER by Persons.lastname

Union and UNION ALL operators

The UNION operator is used to combine the result set of two or more SELECT statements.

By default, the UNION operator chooses a different value. If duplicate values are allowed, use UNION all.

eg

SELECT E_name from Employees_china

UNION

SELECT E_name from Employees_usa

SELECT E_name from Employees_china

UNION All

SELECT E_name from Employees_usa

SQL Advanced (ii) (alias) SQL Join Union and UNION ALL)