Relational MySQL tables for join syntaxes — practise purposes

It’s commonly well-known that MySQL is a good application and, well, free to use so you can imagine the popularity. However, besides the standard statements like SELECT, INSERT, DELETE and UPDATE which are used for the most common basic query’s, there are also some statements called “JOINS”. Actually, joins are quite useful facility’s of SQL. As the most of us know, SQL databases are relational, so information is stored and the relation between records are stored in (most of the time) different tables.

As this is an example, i won’t explain all the statements here, but i provide 4 examples of them as you can see below. At the end you can find a SQL file. Copy and paste it into your favorite text editor and save the file as SQL (.sql), then just import the file or run the query directly on your database without the import story.

Good luck with your exercise!!!

LEFT JOIN

LEFT JOIN. Returns all names including their skills, even if there is no skill assigned, the name of the person will be shown.

$query1 = mysql_query("
        SELECT person_name, skills_short
        FROM persons AS a
        LEFT JOIN relation AS b
        ON a.person_id = b.person_id
        LEFT JOIN skills AS c
        ON b.skills_id = c.skills_id
    ");

while ($r = mysql_fetch_array($query1, MYSQL_ASSOC))
{
    echo $r['person_name'].'--'. $r['skills_short'] .'<br />';
}

LEFT JOIN DELETE

LEFT JOIN. Deletes the rows from persons and relation as the WHERE clause requests

$query1 = mysql_query("
        DELETE a,b
        FROM persons a
        LEFT JOIN relation b
        ON a.person_id = b.person_id
        LEFT JOIN skills c
        ON b.skills_id = c.skills_id
        WHERE a.person_id = 4
    ");

RIGHT JOIN

RIGHT JOIN. Returns all skills, including the names of the persons who can write it.
Even if their is no name assigned to the particular skill, it will be shown without name.

$query1 = mysql_query("
        SELECT person_name, skills_short
        FROM persons AS a
        RIGHT JOIN relation AS b
        ON a.person_id = b.person_id
        RIGHT JOIN skills AS c
        ON b.skills_id = c.skills_id
    ");

while ($r = mysql_fetch_array($query1, MYSQL_ASSOC))
{
    echo $r['person_name'].'--'. $r['skills_short'] .'<br />';
}

INNER JOIN

INNER JOIN. Returns all skills, including the names of the persons who can write it.

$query1 = mysql_query("
        SELECT person_name, skills_short
        FROM persons AS a
        INNER JOIN relation AS b
        ON a.person_id = b.person_id
        INNER JOIN skills AS c
        ON b.skills_id = c.skills_id
    ");

while ($r = mysql_fetch_array($query1, MYSQL_ASSOC))
{
    echo $r['person_name'].'--'. $r['skills_short'] .'<br />';
}

SQL Datafile with relational tables

-- Practise JOIN syntaxes for MySQL
-- Relation tables
-- Source: http://www.online-source.net
-- Author: Laurens ten Ham

CREATE TABLE IF NOT EXISTS persons (
	person_id int(10) unsigned NOT NULL AUTO_INCREMENT,
	person_name varchar(255) NOT NULL,
	PRIMARY KEY (person_id)
)   ENGINE=MyISAM  DEFAULT CHARSET=utf8;

INSERT INTO persons VALUES(1, 'John');
INSERT INTO persons VALUES(2, 'Klaas');
INSERT INTO persons VALUES(3, 'Johny');
INSERT INTO persons VALUES(4, 'Laurens');
INSERT INTO persons VALUES(5, 'Harry');

CREATE TABLE IF NOT EXISTS skills (
	skills_id int(10) unsigned NOT NULL AUTO_INCREMENT,
	skills_name varchar(255) NOT NULL,
	skills_short varchar(255) NOT NULL,
	PRIMARY KEY (skills_id)
)   ENGINE=MyISAM  DEFAULT CHARSET=utf8;

INSERT INTO skills VALUES(1, 'Active server pages', 'ASP');
INSERT INTO skills VALUES(2, 'Hypertext preprocessor', 'PHP');
INSERT INTO skills VALUES(3, 'HyperText markup language', 'HTML');
INSERT INTO skills VALUES(4, 'Standard generalized markup language', 'SGML');
INSERT INTO skills VALUES(5, 'Structured query language', 'SQL');
INSERT INTO skills VALUES(6, 'Extensible markup language', 'XML');
INSERT INTO skills VALUES(7, 'Cascading style sheets', 'CSS');
INSERT INTO skills VALUES(8, 'Java server pages', 'JSP');

CREATE TABLE IF NOT EXISTS relation (
	match_id int(10) unsigned NOT NULL AUTO_INCREMENT,
	person_id int(10) NOT NULL,
	skills_id int(10) NOT NULL,
	PRIMARY KEY (match_id)
)   ENGINE=MyISAM  DEFAULT CHARSET=utf8;

INSERT INTO relation VALUES(1, 4, 2);
INSERT INTO relation VALUES(2, 2, 3);
INSERT INTO relation VALUES(3, 3, 7);
INSERT INTO relation VALUES(4, 3, 6);
INSERT INTO relation VALUES(5, 1, 4);
INSERT INTO relation VALUES(6, 2, 2);
INSERT INTO relation VALUES(7, 1, 1);
INSERT INTO relation VALUES(8, 3, 5);
INSERT INTO relation VALUES(9, 4, 4);
INSERT INTO relation VALUES(10, 2, 5);
INSERT INTO relation VALUES(11, 3, 2);
INSERT INTO relation VALUES(12, 4, 7);
INSERT INTO relation VALUES(13, 1, 6);

• 

Mail this!