Joining ON tables (ANSI)

Here is a quick look at the ANSI JOIN ON syntax.
Plenty of people have looked at this as well. Here are some good, brief desciptions
http://www.w3schools.com/sql/sql_join.asp
http://en.wikipedia.org/wiki/Join_(SQL) (of course!)
http://www.oracle-base.com/articles/9i/ansi-iso-sql-support.php (first part of the article - 9i was the version of Oracle where this was introduced!)

However, I could not resist adding my own humble example.

Imagine the following entities.

Inside Chores we find -

CHORE_ID	CHORE_DESC
10	Change Cat Litter

Inside Tasks we find -

TASK_ID	TASK_DESC	HOW_LONG_HR	CHORE_ID
1	Overcome your revulsion	.1	10
2	Get your supplies (another liner, more cat litter	.2	10
3	Empty Soiled Cat Litter into Garbage	.1	10
4	Replace Liner	.1	10
5	Pour in Litter	.1	10

How do we know which task lines up with which chore?
In tasks, we have CHORE_ID. The value of CHORE_ID in TASKS references CHORE_ID in CHORES.

So we want to join on the rows ON the condition where CHORES.CHORE_ID is the same as TASKS.CHORE_ID. The syntax and results follow.

SELECT *
FROM   chores c
  JOIN tasks t
    ON (c.chore_id = t.chore_id);​

CHORE_ID	CHORE_DESC	TASK_ID	TASK_DESC	HOW_LONG_HR	CHORE_ID
10	Change Cat Litter	1	Overcome your revulsion	.1	10
10	Change Cat Litter	2	Get your supplies (another liner, more cat litter	.2	10
10	Change Cat Litter	3	Empty Soiled Cat Litter into Garbage	.1	10
10	Change Cat Litter	4	Replace Liner	.1	10
10	Change Cat Litter	5	Pour in Litter	.1	10

I would read this as -
SELECT *                          -- show everything
FROM   chores c                   -- from chores
  JOIN tasks t                    -- joining (combining) with tasks
    ON (c.chore_id = t.chore_id);​ -- on the condition where the CHORE_ID is equal in both tables

The "c" and "t" are "Table Aliases" and our way of communicating to the database where a column comes from. In the case of Joins, it is not obvious which table has which column. YOU SHOULD ALWAYS USE table aliases in any join on ALL columns.

I am going to clean up this query a bit. We don't need CHORE_ID in there twice and I am going to make the sort order explicit.

SELECT c.chore_id,
       c.chore_desc      AS CHORE,
       t.task_id         AS STEP,
       t.task_desc       AS DESCRIPTION,
       t.how_long_hr* 60 AS minutes
FROM   chores c
  JOIN tasks t
    ON (c.chore_id = t.chore_id)
ORDER BY c.chore_id ASC, 
         t.task_id  ASC;​

CHORE_ID	CHORE	STEP	DESCRIPTION	MINUTES
10	Change Cat Litter	1	Overcome your revulsion	6
10	Change Cat Litter	2	Get your supplies (another liner, more cat litter	12
10	Change Cat Litter	3	Empty Soiled Cat Litter into Garbage	6
10	Change Cat Litter	4	Replace Liner	6
10	Change Cat Litter	5	Pour in Litter	6

There are also OUTER joins! I'll defer to w3Schools for these.

http://www.w3schools.com/sql/sql_join_left.asp
http://www.w3schools.com/sql/sql_join_right.asp
http://www.w3schools.com/sql/sql_join_full.asp

And we have CROSS and NATURAL joins.
http://www.w3resource.com/sql/joins/cross-join.php
http://www.w3resource.com/sql/joins/natural-join.php

Finally, there is also JOIN USING.
http://stackoverflow.com/questions/16162897/oracle-using-clause-best-practice
(Normally, I would LOVE this syntax but I have a pet peeve with how it handles table aliases on columns being utilized in the USING clause.)

And, as far as WHY you should use ANSI syntax for this, I defer to this article.
http://stackoverflow.com/questions/18891148/oracle-joins-comparison-between-conventional-syntax-vs-ansi-syntax
(Obvious Oracle bias here but the philosophy crosses implementations!)

Syntax to recreate this example is below.

DROP TABLE chores CASCADE CONSTRAINTS;
CREATE TABLE chores
(
  chore_id   NUMBER(3),
  chore_desc VARCHAR2(100)
)
;

ALTER TABLE chores
ADD CONSTRAINT chores_pk
PRIMARY KEY (chore_id);

DROP TABLE tasks CASCADE CONSTRAINTS;
CREATE TABLE tasks
(
  task_id     NUMBER(4),
  task_desc   VARCHAR2(100),
  how_long_hr NUMBER(5,2),
  chore_id    NUMBER(3)
);

ALTER TABLE tasks
ADD CONSTRAINT tasks_pk
PRIMARY KEY (task_id);

ALTER TABLE tasks
ADD CONSTRAINT tasks_chores_fk
FOREIGN KEY (chore_id)
REFERENCES chores(chore_id);

INSERT INTO chores
    (chore_id, chore_desc)
VALUES
    (10,'Change Cat Litter');

INSERT INTO tasks
    (task_id, task_desc, how_long_hr, chore_id)
VALUES
    (1,'Overcome your revulsion',0.1,10);

INSERT INTO tasks
    (task_id, task_desc, how_long_hr, chore_id)
VALUES
    (2,'Get your supplies (another liner, more cat litter',0.2,10);

INSERT INTO tasks
    (task_id, task_desc, how_long_hr, chore_id)
VALUES
    (3,'Empty Soiled Cat Litter into Garbage',0.1,10);

INSERT INTO tasks
    (task_id, task_desc, how_long_hr, chore_id)
VALUES
    (4,'Replace Liner',0.1,10);

INSERT INTO tasks
    (task_id, task_desc, how_long_hr, chore_id)
VALUES
    (5,'Pour in Litter',0.1,10);

Have fun!