Using SQL Joints, you can retrieve data more than one table or view using the keys [primary & foreign] references. |
The syntax for joining multiple tables is as follows:
SELECT column, column..
FROM table1, table2
The SELECT statement contains the columns to retrieve and may came from two or more tables. If the selected column exists both tables, specify the table [table.column]. You have to specify the tables in the FROM clause and the joint condition is done in the WHERE clause. In the WHERE clause, you need the table name and dot followed by column name. The column name in the WHERE clause is the joint column [keys]. AND & OR also normally used to make multiple joint conditions. There are about four basic types of joints, Equality Joints, Outer Joints, Self Joints, & Inequaligyt Joints.
Equality Joints happens when two tables are joined based on values in one table being equal to values in another table. For example, product table and inventory table may have joint column of product_id as it show in the following tables.
|100000000||Printer||Inkjet 300 colour
|100000001||Printer||1220CXI Inkjet Printer
|100000002||Printer||Photo 890 Injet Printer
|100000003||Printer||Photo 890 Injet Printer
The query to perform equality joints might loook like this:
SELECT product.product_id,name,price,qty_on_hand, qty_on_order
FROM product, inventory
Product table is specified in the SELECT statement to issue th product_id and the reason is, product_id exists both tables and if you don't specified which table to select from, you will receive ambiguous error. The query will select all the selected rows from both tables since there is always product_id equal to product_id in the other table.
Outer Joint is joint condition where all the rows of a table are selected along with their matching rows in the other table. For example, you might want select all your customers along with their orders if they have orders. + sign is used in the WHERE clause beside the child table within parentheses. The following is SQL joint statement to select every customer in the customer table along with their orders if they have orders and if they don't an order, it will select blanks.
SELECT customer.customer_id, firstName, lastName, item_id,qty_ordered,price
FROM customer, order
Self Joint is joint of a table by it self. For example, if you want retrieve customers whom ordered same products twice or more assuming there is num_order column that keeps track the number of orders customers made. Here is how you would do this using a self joints:
FROM order o1, order o2
WHERE o1.item_id=o2.item_id AND o1.num_order>1;
This query simply created two table alias, o1 and o2 which represents two copies of the table order then compares if item_id exists both table when order is placed two or more times by a customer.
Inequality joint is when each record in one table is joined with every record in the second table using operators <>, <, >, >=, etc to define an inequality condition. It's opposite of inner joints. This type of joint is rarely used since joint columns are keys & inequality comparison of the keys has no meaningful applications.
Here is an example:
SELECT firstName ||' '||lastName "Full Name"
FROM customer, order
WHERE customer.customer_id < order.customer_id;
|Group Functions Creating Views