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 TagInenglish 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 TagIn englishTag In hindi 1 One 2 Do 2 Two 3 Teen 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);