SQL Joints, Equality Joints, Outer Joints, Self Joints, and Inequality Joints

  :: Home

PC Topics

  :: Build A PC

  :: Windows XP

  :: PC Help

Tutorials

  :: HTML

  :: JavaScript

  :: ASP

  :: PHP

  :: VBScript

  :: SQL

Miscellaneous

  :: Code/Scripts

  :: Forum

  :: Links

  :: Contact us

  :: Tell A Friend

  :: In Somali

  ::

Web Articles

Top Web Hosting
Reviews

Processing
Online Card Payment Guide

Domain name
registration & buying guide

Getting
a website online guide

Search Engine
submission & optimization tips

Quality business correspondence.

Joints

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
WHERE table1.join_column=table2.join_column;
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

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.

Product Table
Product ID	Name	Description	Price	Cost
100000000	Printer	Inkjet 300 colour Printer	120	80
100000001	Printer	1220CXI Inkjet Printer	200	130
100000002	Printer	Photo 890 Injet Printer	250	200
100000003	Printer	Photo 890 Injet Printer	300	270

Inventory Table
Product ID	qty_on_hand	qty_on_order	min_req	mx_req
100000000	20	0	10	25
100000001	10	5	2	15
100000002	2	10	1	12
100000003	1	15	1	15

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
WHERE product.product_id=inventory.product_id;
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 Joints

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
WHERE customer.customer_id=order.customer_id(+);

Self Joints

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:
SELECT o1.customer_id
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 Joints

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

Listed @ ConsumerVote.com - The Consumer Rated Web Directory

PC Articles

Computer Safety
prevent viruses & Spyware