-- mysql script
drop table if exists test_user;
create table test_user (
	id int(11) NOT NULL,
	name varchar(128) NOT NULL,
	primary key (id)
);

drop table if exists test_articles;
create table test_article (
	id int(11) NOT NULL auto_increment,
        userid int(11) NOT NULL,
	title varchar(255) NOT NULL,
        primary key (id),
        key test_contraint_user_article (userid),
        constraint test_contraint_user_article
            foreign key (userid) references
            test_user (id)
);

-- add data
insert into test_user values ('1','Joe');
insert into test_user values ('2','John');
insert into test_user values ('3','Joseph');
insert into test_article (userid, title) values ('1','The diary of Joe');
insert into test_article (userid, title) values ('2','The diary of John');

-- equivalency join

mysql> select u.id, u.name, a.id, a.title 
    from test_user as u, test_article as a 
    where u.id = a.userid;

+----+------+----+-------------------+
| id | name | id | title             |
+----+------+----+-------------------+
|  1 | Joe  |  1 | The diary of Joe  |
|  2 | John |  2 | The diary of John |
+----+------+----+-------------------+


-- the left join which gives precedence 
-- to the 'left' table note that it returns 
-- nulls from the fields on the right hand 
-- table which do not have a foreign 
-- key relationship

mysql> select u.id, u.name, a.id, a.title 
  from test_user as u 
  LEFT JOIN test_article as a 
  ON u.id = a.userid;

+----+--------+------+-------------------+
| id | name   | id   | title             |
+----+--------+------+-------------------+
|  1 | Joe    |    1 | The diary of Joe  |
|  2 | John   |    2 | The diary of John |
|  3 | Joseph | NULL | NULL              |
+----+--------+------+-------------------+


-- to find out the users that have not 
-- written an article yet the query can 
-- search on null

mysql> select u.id, u.name, a.id, a.title 
  from test_user as u 
  LEFT JOIN test_article as a 
  ON u.id = a.userid 
  WHERE a.id is NULL;

+----+--------+------+-------+
| id | name   | id   | title |
+----+--------+------+-------+
|  3 | Joseph | NULL | NULL  |
+----+--------+------+-------+