Joins are an integral part of a relational database. Joins allow the database user to take advantage of the
relationships that were developed in the design phase of the database. A join is the term used to describe the act when two or more tables are "joined" together to retrieve needed data based on the relationships that are shared between them. For example, in your Meet_A_Geek database, you have a relationship between the Customers and Orders tables. To select data out of the Customers table based on some criterion that is contained in the Orders table would require you to use a join of some kind. For example, to retrieve all the customers who have placed an order, you could use the following syntax:
SELECT First_Name, Last_Name
FROM Customer AS C, Orders AS O
WHERE C.Customer_ID = O. Customer_ID
ANSI-92 Compliance

__________________
- D.Rajinikanth
**Born to Shine**

CROSS JOIN
The CROSS JOIN is not used very much at all. In fact, most of the time it is done by mistake. A CROSS JOIN returns all the records from all the tables mentioned in the JOIN. This is also referred to as a Cartesian join]. These joins are very processor-intensive and should be avoided. The syntax for a CROSS JOIN would look like the following:
The old way:

SELECT C.First_Name, C.Last_Name, O.Order_ID FROM Customers as C, Orders as O
The ANSI-92 way:

SELECT C.First_Name, C.Last_Name, O.Order_ID FROM Customers as C CROSS JOIN Orders as O

The resultset that is returned from this type of join is huge. It basically returns all the data in all the tables.

INNER JOIN.
INNER JOINs are probably the most common of all joins.
An INNER JOIN simply means that all records that are unmatched are discarded. Only the matched rows are displayed in the resultset. This is the default type of join, so the word INNER is optional. This type of join is based on the criteria in the JOIN clause. Your first example was an example of an INNER JOIN:
SELECT C.First_Name, C.Last_Name, O.Order_ID
FROM Customer as C
(INNER) JOIN Orders as O ON C.Customer_ID = O.Customer_ID

Again, the word INNER appears in parentheses because it is optional. The old way to create an INNER
JOIN would look like the following:

SELECT First_Name, Last_Name, O.Order_ID FROM Customer as C, Order as
O WHERE C.Customer_ID = O.Customer_ID

This is probably the most frequently used of all joins—the primary reason why it's the ANSI-92 default.

__________________
Regards,
VELHARI
I am not totally useless. I can be used for a bad example

LEFT JOIN
A LEFT JOIN returns all rows from the left table in a join. For example, if you were to continue using your Customers and Orders example, the following statement shows the syntax for a LEFT JOIN:

SELECT C.First_Name, C.Last_Name, O.Order_ID FROM Customer as C LEFT JOIN Order as O ON C.Customer_ID = O.Customer_ID

In this example, all the rows from the table on the left side of the equation will be returned, regardless of whether they have a match with the table on the right side. If there is no match, a NULL value will be returned. In other database systems, the LEFT JOIN is implemented the "old way" using a symbol. The symbol is an asterisk (*) equal sign (=) combination. The placement of the asterisk (left or right of the equal sign) indicates the type of join. So, to implement a LEFT JOIN, the following would be the syntax:
SELECT C.First_Name, C.Last_Name, O.Order_ID FROM Customers AS C, Orders as O WHERE C.Customer_ID *= O.Orders_ID

NATURAL LEFT JOIN
The NATURAL LEFT JOIN is the same as a regular LEFT JOIN except that it automatically uses all the matching columns as part of the join. It is syntactically equivalent to a LEFT JOIN with a USING clause that names all the identical columns of the two tables. The syntax looks like the following:

SELECT C.First_Name, C.Last_Name, O.Orders_ID
FROM Customers as C
NATURAL LEFT JOIN Orders as O

This would return all the rows from the Customers table, regardless of whether they had a matching record in any of the same columns of the Orders table. The Orders table would return a NULL if it did not have a match. Again, this join is rarely used. The only way to accomplish this type of join is to use the given syntax. Joins are fairly straightforward, But don't let the syntax fool you. Joins allow you to take advantage of the relationships you defined earlier when you created the tables. Practice and experience will aid greatly when creating joins

__________________
With,
J. Jeyaseelan
Everything Possible

In this Virtual Workshop we will look at retrieving data from a relational database structure, i.e. with multiple tables, using the SQL JOIN Syntax.

jack
_________________________________________

steripen handheld Girls Games