The JOIN keyword is used in an SQL statement to query data from two or more tables, based on a relationship between certain columns in these tables. [@via w3schools]
A SQL JOIN clause combines records from two or more tables in a database. It creates a set that can be saved as a table or used as is. A JOIN is a means for combining fields from two tables by using values common to each. ANSI standard SQL specifies four types of JOINs: INNER, OUTER, LEFT, and RIGHT. In special cases, a table (base table, view, or joined table) can JOIN to itself in a self-join. [@via wikipedia]
SQL Joins are helpful when we have to fetch data with a single query from two or more database tables.
Case:
I need to store news/article information. The news can also have comments and tags. News can be submitted/posted by multiple users.
According to the above scenario, I have created 5 tables and inserted some data into them :-
news – to store news
comments – to store comments for any particular news
tags – to store tags associated with any particular news
users – to store user information
tags_news – to store relationship between news and tags
—
— Table structure for table comments
—
CREATE TABLE comments
(
id
bigint(20) NOT NULL auto_increment,
news_id
bigint(20) NOT NULL,
user_id
bigint(20) NOT NULL,
detail
text NOT NULL,
status
tinyint(1) NOT NULL default '1',
comment_date
int(11) NOT NULL,
PRIMARY KEY (id
),
KEY user_id
(user_id
),
KEY news_id
(news_id
)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
—
— Dumping data for table comments
—
INSERT INTO comments
(id
, news_id
, user_id
, detail
, status
, comment_date
) VALUES
(1, 1, 2, 'nice post :)', 1, 1273431296),
(2, 2, 2, 'hahaha', 1, 1273431420),
(3, 3, 2, '3123123', 1, 1273431452),
(4, 1, 2, 'thank You', 1, 1273431475),
(5, 2, 1, 'congratulations!', 1, 1273431500);
— ——————————————————–
—
— Table structure for table news
—
CREATE TABLE news
(
id
bigint(20) NOT NULL auto_increment,
user_id
bigint(20) NOT NULL,
title
text NOT NULL,
detail
text NOT NULL,
visit
int(11) NOT NULL default '0',
status
tinyint(1) NOT NULL default '1',
created_date
int(11) NOT NULL,
PRIMARY KEY (id
),
KEY user_id
(user_id
)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
—
— Dumping data for table news
—
INSERT INTO news
(id
, user_id
, title
, detail
, visit
, status
, created_date
) VALUES
(1, 1, 'Extra Content', 'fasdfasdf', 2, 1, 1273431296),
(2, 1, 'My My Question', 'this is jpt question.. :D', 16, 1, 1273431389),
(3, 2, 'Am I ram?', 'I am ram..yahoo !!', 3, 1, 1273431420);
— ——————————————————–
—
— Table structure for table tags
—
CREATE TABLE tags
(
id
bigint(20) NOT NULL auto_increment,
name
varchar(255) NOT NULL,
slug
varchar(255) NOT NULL,
PRIMARY KEY (id
)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
—
— Dumping data for table tags
—
INSERT INTO tags
(id
, name
, slug
) VALUES
(1, 'sagarmatha', 'sagarmatha'),
(2, 'nepal', 'nepal'),
(3, 'gautam buddha', 'gautam-buddha'),
(4, 'testing', 'testing'),
(5, 'tags', 'tags'),
(6, 'tasty apple', 'tasty-apple'),
(7, 'banana', 'banana');
— ——————————————————–
—
— Table structure for table tags_news
—
CREATE TABLE tags_news
(
id
bigint(20) NOT NULL auto_increment,
news_id
bigint(20) NOT NULL,
tags_id
bigint(20) NOT NULL,
PRIMARY KEY (id
),
KEY news_id
(news_id
)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
—
— Dumping data for table tags_news
—
INSERT INTO tags_news
(id
, news_id
, tags_id
) VALUES
(1, 1, 1),
(2, 1, 2),
(3, 1, 3),
(4, 2, 4),
(5, 2, 5),
(6, 2, 6),
(7, 3, 6),
(8, 3, 7);
— ——————————————————–
—
— Table structure for table users
—
CREATE TABLE users
(
id
bigint(20) NOT NULL auto_increment,
user_id
bigint(20) NOT NULL,
firstname
varchar(100) NOT NULL,
lastname
varchar(100) NOT NULL,
PRIMARY KEY (id
),
KEY user_id
(user_id
)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
—
— Dumping data for table users
—
INSERT INTO users
(id
, user_id
, firstname
, lastname
) VALUES
(1, 1, 'Mukesh', 'Chapagain'),
(2, 2, 'Christopher', 'Gayle'),
(3, 3, 'Brian', 'Lara');
— ——————————————————–
Now, by using SQL JOINS, I can fetch data from these tables for different conditions and in different ways. Here follows the different SQL Queries with the magical JOIN statements:-
1) SELECT NEWS WITH COMMENTS COUNT FOR EACH NEWS
SELECT n.*, ifnull(c.count,0) AS comment FROM news AS n
LEFT JOIN
(SELECT COUNT(comments.id) AS count, comments.news_id FROM comments GROUP BY comments.news_id) AS c
ON n.id = c.news_id
ORDER BY n.created_date DESC;
2) SELECT NEWS POSTED BY ANY PARTICULAR USER (HERE, user_id = 1)
SELECT n.* FROM news AS n
INNER JOIN
users AS u
ON n.user_id = u.id
WHERE u.id = 1;
3) SELECT NEWS POSTED BY ANY PARTICULAR USER (WITH USER’S FIRSTNAME AND LASTNAME) (HERE, user_id = 1)
SELECT n.*, u.firstname AS firstname, u.lastname AS lastname FROM news AS n
INNER JOIN
users AS u
ON n.user_id = u.id
WHERE u.id = 1;
4) SELECT NEWS POSTED BY ANY PARTICULAR USER (WITH COMMENTS COUNT FOR EACH NEWS) (HERE, user_id = 1)
SELECT n.*, ifnull(c.count,0) AS comment, u.firstname AS firstname, u.lastname AS lastname FROM news AS n
LEFT JOIN
(SELECT COUNT(comments.id) AS count, comments.news_id FROM comments GROUP BY comments.news_id) AS c
ON n.id = c.news_id
INNER JOIN
users AS u
ON n.user_id = u.id
WHERE u.id = 1;
5) SELECT COMMENTS FOR ANY PARTICULAR NEWS (HERE, news_id =1)
SELECT c.* FROM comments AS c
INNER JOIN
news AS n
ON c.news_id = n.id
WHERE c.news_id = 1;
6) SELECT COMMENTS FOR ANY PARTICULAR NEWS (ALONG WITH USER INFORMATION) (HERE, news_id = 1)
SELECT c.*, u.firstname AS firstname, u.lastname AS lastname FROM comments AS c
INNER JOIN
news AS n
ON c.news_id = n.id
INNER JOIN
users AS u
ON c.user_id = u.id
WHERE c.news_id = 1;
7) SELECT TAGS FOR ANY PARTICULAR NEWS (HERE, news_id = 1)
SELECT t.* FROM tags AS t
INNER JOIN
(SELECT tags_news.news_id AS news_id, tags_news.tags_id FROM tags_news WHERE tags_news.news_id = 1) AS tn
ON t.id = tn.tags_id;
8) SELECT NEWS BY TAG NAME (HERE, tag = ‘nepal’)
SELECT n.*, t.name AS tag_name, t.slug AS tag_slug FROM news AS n
INNER JOIN
tags_news as tn
ON n.id = tn.news_id
INNER JOIN tags AS t
ON tn.tags_id = t.id
WHERE t.slug = 'nepal';
9) SELECT NEWS BY TAG NAME (ALONG WITH COMMENTS COUNT) (HERE, tag = ‘nepal’)
SELECT n.*, ifnull(c.count,0) AS comment, t.name AS tag_name, t.slug AS tag_slug, t.id AS tag_id FROM news AS n
INNER JOIN
tags_news AS tn
ON (n.id = tn.news_id)
INNER JOIN tags AS t
ON (tn.tags_id = t.id)
LEFT JOIN
(SELECT COUNT(comments.id) AS count, comments.news_id FROM comments
GROUP BY comments.news_id) AS c
ON n.id = c.news_id
WHERE t.slug = 'nepal'
ORDER BY n.created_date DESC;
10) SELECT TAG CLOUD
SELECT t . * , IFNULL( tq.count, 0 ) AS count
FROM tags AS t
LEFT JOIN (
SELECT COUNT( tags_news.id ) AS count, tags_news.tags_id
FROM tags_news
GROUP BY tags_news.tags_id
) AS tq ON t.id = tq.tags_id
ORDER BY t.name ASC
LIMIT 15;
Hope this helps. And thanks for reading.