Informatics Practices for Class XI (CBSE)/Joins and Subqueries

Joins

The Most important aspect of SQL is its relational features. You can query, compare and calculate two different tables having entirely different structure. Joins and subselects are the two methods to join tables. Both methods of joining tables should give the same results. The natural join is faster on most SQL platforms.

In the following example a student is trying to learn what the numbers are called in hindi.

CREATE TABLE english (Tag int, Inenglish varchar(255));
CREATE TABLE hindi (Tag int, Inhindi varchar(255));

INSERT INTO english (Tag, Inenglish) VALUES (1, 'One');
INSERT INTO english (Tag, Inenglish) VALUES (2, 'Two');
INSERT INTO english (Tag, Inenglish) VALUES (3, 'Three');

INSERT INTO hindi (Tag, Inhindi) VALUES (2, 'Do');
INSERT INTO hindi (Tag, Inhindi) VALUES (3, 'Teen');
INSERT INTO hindi (Tag, Inhindi) VALUES (4, 'Char');

	select * from english		select * from hindi
Tag	Inenglish	Tag	Inhindi
1	One	2	Do
2	Two	3	Teen
3	Three	4	Char

Cartesian join

A Cartesian join is when you join every row of one table to every row of another table.

SELECT * FROM english, hindi

It is also called Cross Join and may be written in this way:

SELECT * FROM english CROSS JOIN hindi

Tag	Inenglish	Tag	Inhindi
1	One	2	Do
2	Two	2	Do
3	Three	2	Do
1	One	3	Teen
2	Two	3	Teen
3	Three	3	Teen
1	One	4	Char
2	Two	4	Char
3	Three	4	Char

Inner Join

SELECT hindi.Tag, english.Inenglish, hindi.Inhindi
FROM english, hindi
WHERE english.Tag = hindi.Tag

Tag	Inenglish	Inhindi
2	Two	Do
3	Three	Teen

You can also write the same query as

SELECT hindi.Tag, english.Inenglish, hindi.Inhindi
FROM english INNER JOIN hindi
ON english.Tag = hindi.Tag

Natural Joins using "using" (Compatible: MySQL 4+; but changed in MySQL 5) The following statement using "USING" method will display the same results.

SELECT hindi.tag, hindi.Inhindi, english.Inenglish
FROM hindi NATURAL JOIN english
USING (Tag)

Outer Joins

Tag	Inenglish	Tag	Inhindi
1	One
2	Two	2	Do
3	Three	3	Teen
		4	Char

LEFT JOIN / LEFT OUTER JOIN

The syntax is as follows:

SELECT field1, field2 FROM table1 LEFT JOIN table2 ON field1=field2

SELECT e.Inenglish as English, e.Tag, '--no row--' as Hindi
FROM english AS e LEFT JOIN hindi AS h
ON e.Tag=h.Tag 
WHERE h.Inhindi IS NULL

English  tag   Hindi
One      1     --no row-

Right Outer Join

SELECT '--no row--' AS English, h.tag, h.Inhindi AS Hindi
FROM english AS e RIGHT JOIN hindi AS h
ON e.Tag=h.Tag
WHERE e.Inenglish IS NULL

English tag Hindi --no row-- 4 Char

• Make sure that you have the same name and same data type in both tables.
• The keywords LEFT and RIGHT are not absolute, they only operate within the context of the given statement: we can reverse the order of the tables and reverse the keywords, and the result would be the same.
• If the type of join is not specified as inner or outer then it will be executed as an INNER JOIN.

Full Outer Join

As for v5.1, MySQL does not provide FULL OUTER JOIN. You may emulate this using a series of UNIONed SELECT statements.

Multiple joins

It is possible to join more than just two tables:

SELECT ... FROM a JOIN (b JOIN c on b.id=c.id) ON a.id=b.id

Here is an example from Savane:

mysql> SELECT group_type.type_id, group_type.name, COUNT(people_job.job_id) AS count 
       FROM group_type
        JOIN (groups JOIN people_job ON groups.group_id = people_job.group_id) 
        ON group_type.type_id = groups.type
       GROUP BY type_id ORDER BY type_id
+---------+--------------------------------------+-------+
| type_id | name                                 | count |
+---------+--------------------------------------+-------+
|       1 | Official GNU software                |   148 |
|       2 | non-GNU software and documentation   |   268 |
|       3 | www.gnu.org portion                  |     4 |
|       6 | www.gnu.org translation team         |     5 |
+---------+--------------------------------------+-------+
4 rows in set (0.02 sec)

Subqueries

(Compatible: Mysql 4.1 and later...Bold text)

• SQL subqueries let you use the results of one query as part of another query.
• Subqueries are often natural ways of writing a statement.
• Let you break a query into pieces and assemble it.
• Allow some queries that otherwise can't be constructed. Without using a subquery, you have to do it in two steps.
• Subqueries always appear as part of the WHERE (or HAVING) clause.
• Only one field can be in the subquery SELECT. It means Subquery can only produce a single column of data as its result.
• ORDER BY is not allowed; it would not make sense.
• Usually refer to name of a main table column in the subquery.
• This defines the current row of the main table for which the subquery is being run. This is called an outer reference.

For e.g. If RepOffice= OfficeNbr from Offices table, list the offices where the sales quota for the office exceeds the sum of individual salespersons' quotas

SELECT City FROM Offices WHERE Target > ???

??? is the sum of the quotas of the salespeople, i.e.

SELECT SUM(Quota)
FROM SalesReps 
WHERE RepOffice = OfficeNbr

We combine these to get

SELECT City FROM Offices 
WHERE Target > (SELECT SUM(Quota) FROM SalesReps 
WHERE RepOffice = OfficeNbr)

Display all customers with orders or credit limits > $50,000. Use the DISTINCT word to list the customer just once.

SELECT DISTINCT CustNbr 
FROM Customers, Orders 
WHERE CustNbr = Cust AND (CreditLimit>50000 OR Amt>50000);