[SQL] Join

on

INNER JOIN gets all records from one table that have some related entry in a second table

LEFT JOIN gets all records from the LEFT linked table but if you have selected some columns from the RIGHT table, if there is no related records, these columns will contain NULL

RIGHT JOIN is like the above but gets all records in the RIGHT table

FULL JOIN gets all records from both tables and puts NULL in the columns where related records do not exist in the opposite table

SELECT <select_list> FROM TableA A LEFT JOIN TableB B ON A.Key = B.Key
SELECT <select_list> FROM TableA A RIGHT JOIN TableB B ON A.Key = B.Key

SELECT <select_list> FROM TableA A LEFT JOIN TableB B ON A.Key = B.Key WHERE B.Key IS NULL
SELECT <select_list> FROM TableA A RIGHT JOIN TableB B ON A.Key = B.Key WHERE A.Key IS NULL

SELECT <select_list> FROM TableA A INNER JOIN TableB B ON A.key = B.key
SELECT <select_list> FROM TableA A FULL OUTER JOIN TableB B ON A.key = B.key
SELECT <select_list> FROM TableA A FULL OUTER JOIN TableB B ON A.key = B.key
WHERE A.key IS NULL OR B.key IS NULL

[code language=”sql”]
CREATE TABLE IF NOT EXISTS `ordinal_english` (
`ordinal_english_id` int(11) NOT NULL AUTO_INCREMENT,
`ordinal_english_text` varchar(20) NOT NULL,
PRIMARY KEY (`ordinal_english_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;

INSERT INTO `ordinal_english` (`ordinal_english_id`, `ordinal_english_text`) VALUES
(1, ‘One’),(2, ‘Two’),(3, ‘Three’),(4, ‘Four’),(5, ‘Five’),(6, ‘Six’);

CREATE TABLE IF NOT EXISTS `ordinal_french` (
`ordinal_french_id` int(11) NOT NULL AUTO_INCREMENT,
`ordinal_french_text` varchar(20) NOT NULL,
PRIMARY KEY (`ordinal_french_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

INSERT INTO `ordinal_french` (`ordinal_french_id`, `ordinal_french_text`) VALUES
(1, ‘Forma’),(3, ‘Pridda’),(4, ‘Feorda’),(5, ‘Fifta’),(6, ‘Siexta’);
<br/>
CREATE TABLE IF NOT EXISTS `ordinal_old_english` (
`ordinal_english_id` int(11) NOT NULL AUTO_INCREMENT,
`ordinal_old_english_text` varchar(20) NOT NULL,
PRIMARY KEY (`ordinal_english_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=10 ;

INSERT INTO `ordinal_old_english` (`ordinal_english_id`, `ordinal_old_english_text`) VALUES
(1, ‘Forma’),(3, ‘Pridda’),(4, ‘Feorda’),(5, ‘Fifta’),(6, ‘Siexta’),(8, ‘Eahtoda’),(9, ‘Scofoda’);
[/code]

That is the database tables. Now to understand the join sql query, run these query on these data.

Left Joins:

[code language=”sql”]
SELECT ordinal_english_text, ordinal_french_text FROM ordinal_english LEFT JOIN ordinal_french ON ordinal_english_id = ordinal_french_id;
[/code]

Left join with USING clause:

[code language=”sql”]SELECT ordinal_english_text , ordinal_old_english_text FROM ordinal_english LEFT JOIN ordinal_old_english USING(ordinal_english_id);
[/code]

Right Joins:

[code language=”sql”]SELECT ordinal_english_text , ordinal_french_text FROM ordinal_english RIGHT JOIN ordinal_french ON ordinal_english_id = ordinal_french_id;[/code]

Right Joins with USING clause:

[code language=”sql”]SELECT ordinal_english_text , ordinal_old_english_text FROM ordinal_english RIGHT JOIN ordinal_old_english USING(ordinal_english_id);[/code]

Leave a Reply

Your email address will not be published. Required fields are marked *