tsunami

log in
history

Structured Query Language

gimmie dat gum
2008-02-11 09:36 UTC

Straight Joins
1) Join the parent table b2b_orders to the child table b2b_items with a straight join, return all the fields
  • ("how?") <-- you ask like this
    • and here's your answer <-- I answer like this
c

2) Join the parent table b2b_orders to the child table b2b_items with a straight join, return the fields from name and start_date from the b2b_orders table and the fields item_num, sku, quantity and price from the b2b_items table where b2b_orders.id is 2 and order the returned records by item_num

3) This is a four table join. Join the parent table b2b_orders to the child table b2b_items with a straight join, then join b2b_items to b2b_products and b2b_products to b2b_products_line, return the fields from name and start_date from the b2b_orders table and the fields item_num, sku, quantity and price from the b2b_items table and order the returned records by item_num
LEFT JOINS
4) Join the b2b_product_line with a LEFT JOIN to the b2b_products (child table) where the product line is 1 and order by sku
Trying to figure out the difference between Straight Joins and Left Joins
STRAIGHT_JOIN is similar to JOIN, except that the left table is always read before the right table. This can be used for those (few) cases for which the join optimizer puts the tables in the wrong order.

example
SELECT * FROM table1, table2;

SELECT * FROM table1 INNER JOIN table2 ON table1.id=table2.id;



The result of a left outer join for tables A and B always contains all records of the "left" table (A), even if the join-condition does not find any matching record in the "right" table (B). This means that if the ON clause matches 0 (zero) records in B, the join will still return a row in the result—but with NULL in each column from B. This means that a left outer join returns all the values from the left table, plus matched values from the right table (or NULL in case of no matching join predicate).


example
SELECT *  
FROM   employee 
       LEFT OUTER JOIN department 
          ON employee.DepartmentID = department.DepartmentID

SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id;

SELECT * FROM table1 LEFT JOIN table2 USING (id);

SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id
  LEFT JOIN table3 ON table2.id=table3.id;