Joining Relations in SQL

1. Joining Relationsin SQL Objectives of the Lecture : • To consider the Natural & Generalised Joins using the SQL1 standard; • To consider the Natural & Generalised Joins using the SQL2 standard.

2. Joins in SQL • Expressing joins in SQL has been particularly affected by two particular SQL standards : • SQL1 Standard SQL, introduced in 1989 - this has no specific support for joins; • SQL2 Standard SQL introduced in 1992 - this has special support for joins. The SQL3 StandardSQLintroduced in 1999 maintains this support. • It is important to know how to use both variants of SQL. • As the SQL1 standard is a subset of SQL2, it is always possible to write an SQL1 style join in SQL2. • Oracle SQL 9i supports SQL2 standard joins, but earlier versions of Oracle only meet the SQL1 standard.

3. SQL1 : Generalised Join Syntax The Generalised Join of relations R and S has the syntax : Select*From R, S Wheretheta-join condition; Principles : • Put Select * to get all the attributes in the result. • Put both relation names in the From phrase. • Put the complete theta join condition in the Where phrase. The SQL statement also retrieves the result from the DB. SQL fulfills all the requirements of the Generalised Join operation.

4. SQL1 : Natural Join Syntax The Natural Join of relations R and S has the syntax : Selectthe result’s column namesFrom R, S Whereequi-join condition; Principles : • Put Select COLUMN_NAMES to get all the columns in the result. • Put both relation names in the From phrase. • Put the complete equi join condition in the Where phrase. The SQL statement also retrieves the result from the DB. SQL fulfills all the requirements of the Natural Join operation. Omit duplicatecolumns. Prefixed by TABLE_NAME. Always prefixed withTABLE_NAME.

5. Examples : SQL1 Generalised Joins SQL1 equivalents of previous examples : • Select *From R, SWhere B < C ; • Select *From R, SWhere A > E And B <> D ; As the operands have no column names in common, it is safe to use“*”in the Select phrase and omit table name prefixes in the Where phrase.

6. Example : SQL1 Natural Join SQL equivalent of previous example : • Select PNo, Qty, SHIP.SNo, SName, From SHIP, SUPPWhereSHIP .SNo =SUPP .SNo ; Or • Select PNo, Qty, SUPP.SNo, SName, From SHIP, SUPPWhereSHIP .SNo =SUPP .SNo ; Doesn’t matter fromwhich table the “SNo”column comes. The order in which the tables appear in the From phrase, and which“SNo” column appears on which side of the “=”, don’t matter.

7. Combining Algebra Operators • Typically we want to join together 2 relations holding relevant data, and then prune the result down with a projection and restriction to yield just the required data : R Join[ Att ] S Restrict[ condition ] Project[ AttNames ] • In SQL, put the Projected attributes in the Select phrase, the Joined relations in the From phrase, and And the Join and Restrict conditions together in the Where phrase, as follows : Select Distinct AttNamesFromR, S Where ( R.Att = S.Att ) And (condition ) ; • SQL’s built-in sequence of operations will execute a Cartesian Product of R and S, then a Restrict on the result using the entireWhere condition, & finally a Project on that result using the Select attributes. Join condition Restrictcondition

8. Examples of Combining Operators Example :Get the supplier’s name who supplies parts in quantities of 10. SHIP Join[ SNo ] SUPP Restrict[ Qty = 10 ] Project[ SName ] Select Distinct SnameFrom SHIP, SUPPWhere SHIP.SNo = SUPP.SNo And Qty = 10 ; Example :Get the names of employees who own a Corsa 1.3. CAR Gen[ Owner = ENo ] EMPLOYEE Restrict[ Type = ‘Corsa 1.3’ ] Project[ EName ] Select Distinct ENameFrom CAR, EMPLOYEEWhere Owner = ENo And Type = ‘Corsa 1.3’ ;

9. Designing SQL Queries • Decide which DB relations contain data that will be required in the answer to the query, and join all those relations together with the appropriate Natural/GeneralisedJoin operation(s). • Remove any unrequired tuples with Restrict operation(s). In principle only one Restrict operation is required, but it may be more convenient to use several. • Remove any unrequired attributes with a Project operation; only one Project operation will be necessary. • Complete the appropriate SQL phrases with the relevant information from the algebra operations : Select …… From .…… Where ( ……… ) And ( ……… ) ; Project attributes Joincondition Restrictcondition Tables tobe joined

10. SQL : Cartesian Product • SQL1 executes a Cartesian Product operation given the following syntax : Select *FromR,S; • Hence the absence of a join condition in the Where phrase causes SQL to execute a Cartesian Product : • If a Cartesian Product is actually needed in a query instead of a Natural or Generalised Join, then just omit the Join condition from the Where phrase. • If a Join condition is accidentally omitted from the Where phrase by error, then the result will be unexpectedly (very) large due to a Cartesian Product operation ! • SQL2 actually has a Cartesian Product operator, with syntax : Select *FromRCross JoinS;

11. SQL2 : Generalised Join Syntax The Generalised Join of relations R and Shas thesyntax : Select * From RJoinSOn ( theta-joincondition ); Principles : • Put Select * to get all the attributes in the result. • PutRJoinSOn ( theta-joincondition )in the From phrase, where R and S are the operands and( theta-joincondition ) is the complete generalised join condition. • No Where phrase is required. The SQL statement also retrieves the result from the DB. SQL fulfills all the Generalised Join requirements.

12. Examples : SQL2 Generalised Joins SQL2 equivalents of previous examples : • Select * From RJoinSOn ( B < C ); • Select * From RJoinSOn ( A > E And B <> D ) ; As the operands have no column names in common, it is safe to use“*”in the Select phrase and omit table name prefixes in the Where phrase.

13. SQL2 : Natural Join Syntax There are 2 ways of writing a NaturalJoin of operands R and S in SQL2 : • Select * From RNaturalJoinS; • Select * From RJoinSUsing ( AttributeName(s) ); Principles : • These are the same as for Generalised Join, except that a different required expression is put in the From phrase. The SQL statement also retrieves the result from the DB. Both variants fulfill all the Natural Join requirements. The attributes on which the ‘=‘comparison(s) is/are made.

14. Examples : SQL2 Natural Joins SQL2 equivalents of a previous example : • Select * From SHIPNaturalJoinSUPP; • Select * From SHIPJoinSUPPUsing ( SNo );

15. SQL2 : Join Problem (1) • Select * From CARNaturalJoinEMPLOYEE; • Select * From CARJoinEMPLOYEEUsing ( Owner, ENo ); Neither will work !Columns “Owner “ and “ENo” don’t appear in both tables. So use an SQL Generalised Join to express the required join, & remove the duplicate data in the Select phrase : • Select RegNo, Type, Owner, EName, M-S, Sal From CARJoinEMPLOYEEOn ( Owner = ENo ); Could have omitted “Owner” instead of “ENo” in Select phrase.

16. SQL2 : Join Problem (2) • Consider the join expressed as :R Join S Using ( J1 ) • Suppose there are two attributes, namedJ1 and J2, both of which appear in R and in S, and are type compatible. • The join will be carried out just using J1, as specified.==> the result will have two attributes called J2 in it. • There are 2 considerations concerning the result : • If a real join requires bothJ1andJ2, then SQL will have generated the wrong result (unless by chance the data in the tables avoids this). • If the problem was unhelpful column names, so that the correct result was generated, the two columns can be distinguished with their table name prefix in the Select phrase.

17. Combining Algebra Operators Follow the same procedure is as before, but using SQL2 syntax. Example : SHIP Join[ SNo ] SUPP Restrict[ Qty = 10 ] Project[ SName ] Select Distinct Sname From SHIP Natural Join SUPP Where Qty = 10 ; Example : CAR Gen[ Owner = ENo ] EMPLOYEE Restrict[ Type = ‘Corsa 1.3’ ] Project[ EName ] Select Distinct ENameFrom CAR Join EMPLOYEE On (Owner = ENo) Where Type = ‘Corsa 1.3’ ; becomes OrSHIP Join SUPP Using(SNo) becomes