Natural join - sql, PL-SQL Programming

Assignment Help:

Natural Join - SQL

In the absence of NATURAL JOIN Example has to be replaced by something rather more longwinded, as shown in Example.

Example: Joining IS_CALLED and IS_ENROLLED_ON in original SQL

SELECT IC.StudentId, Name, CourseId

FROM IS_CALLED AS IC, IS_ENROLLED_ON AS IE

WHERE IC.StudentId = IE.StudentId

866_Joining in SQL.png

Explanation

  • The FROM clause now has two elements. When there are two elements, t1 and t2, the result is equivalent to t1 CROSS JOIN t2, which is SQL's counterpart of t1 TIMES t2 in Tutorial D. However, TIMES requires its operands to have disjoint headings, whereas CROSS JOIN is defined for all pairs of SQL tables. When t1 and t2 each have a column named c, the result has two columns named c. In general, when t1 has m columns named c and t2 has n, t1 CROSS JOIN t2 has m+n columns named c.
  • Following the FROM clause is a WHERE clause, denoting an invocation of the operator WHERE. The operands are the table resulting from the FROM clause and the condition following the word WHERE. SQL's WHERE operator is equivalent to Tutorial D's operator of the same name when its table operand represents a relation.
  • The result of the FROM clause has two columns of the same name, StudentId. The condition specified in the WHERE clause uses range variables, IC and IE, to distinguish between these two columns. The distinction is possible here, thanks to the fact that the same column name isn't used more than once in either of the two operand tables (as we shall see later, that is a condition that does not always apply, even though the same column name cannot be used more than once in a base table).
  • The range variables are defined in the FROM clause alongside the table expressions to which they apply. The key word AS separating the table expression from the range variable name is optional. If the table expression consists of just a table name, unaccompanied by a range variable, then that table name serves also as a range variable name.
  • A range variable is so-called because it is considered to "range over" each element in turn of a collection, the collection in the example at hand being the rows of a table. Note carefully that although the expression IE.StudentId is a column reference, it is not a column name. It references a particular column named StudentId. The prefix "IE." is required because without it the column reference would be ambiguous.

Related Discussions:- Natural join - sql

Parameter and keyword description - exit statement, Parameter and Keyword D...

Parameter and Keyword Description: EXIT: An unconditional EXIT statement (i.e., one without a WHEN clause) exits the present loop instantly. The Execution resumes with th

Name resolution-naming conventions, Name Resolution In potentially uncer...

Name Resolution In potentially uncertain SQL statements, the names of the database columns take precedence over the names of the local variables and formal parameters. For e.g.

Closest approximation to relational union - sql, Closest Approximation to R...

Closest Approximation to Relational Union - SQL Actually, just as SQL has several varieties of JOIN, it also has several varieties of UNION, none of which is equivalent to th

Explicit cursors, Explicit Cursors The set of rows returned by the que...

Explicit Cursors The set of rows returned by the query can include zero, one, or multiple rows, depending on how many rows meet your search criteria. Whenever a query returns

Explicit cursors, Explicit Cursors The set of rows returned by the que...

Explicit Cursors The set of rows returned by the query can include zero, one, or multiple rows, depending on how many rows meet your search criteria. Whenever a query returns

Providing results of queries, Providing Results of Queries Expressing ...

Providing Results of Queries Expressing queries in SQL is the (big) subject. Here I present just a simple example to give you the flavour of things to come in those chapters.

Using lock table, Using LOCK TABLE You use the LOCK TABLE statement to...

Using LOCK TABLE You use the LOCK TABLE statement to lock the whole database tables in the specified lock mode so that you can share or deny the access to them. For illustrati

Package standard in pl/sql, Package STANDARD package named STANDARD d...

Package STANDARD package named STANDARD defines the PL/SQL atmosphere. The package specification globally declares the exceptions, types, and subprograms that are available a

Logical connectives - sql, Logical Connectives - SQL SQL's extended t...

Logical Connectives - SQL SQL's extended truth tables in which the symbol, for unknown, appears along with the usual T and F. Negation (NOT, ¬) Conjunction (

Manipulating collections-nested table examples, Manipulating Collections ...

Manipulating Collections Within PL/SQL, the collections add procedural power and flexibility. The biggest benefit is that your program can compute subscripts to process the spec

Write Your Message!

Captcha
Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd