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

How pl/sql resolves the calls? , How Calls Are Resolved? The figure sho...

How Calls Are Resolved? The figure shows that how the PL/SQL compiler resolves the subprogram calls. When the compiler encounters the procedure or function call, it tries to di

Recursion, Recursion The Recursion is a powerful method for simplify th...

Recursion The Recursion is a powerful method for simplify the design of the algorithms. Principally, the recursion means the self-reference. In the recursive mathematical serie

Use the returning clause -improve performance of application, Use the RETUR...

Use the RETURNING Clause Frequently, the application requires information about the row affected by a SQL operation, for illustration, to produce a report or take a subsequent

Like operator-comparison operators, LIKE Operator You use the LIKE opera...

LIKE Operator You use the LIKE operator to compare the character value to a pattern. The Case is significant. LIKE returns the Boolean value TRUE when the character patterns mat

Null statement-sequential control, NULL Statement The NULL statement c...

NULL Statement The NULL statement clearly specifies in action; it does nothing other than to pass control to the next statement. It can, though, improve the readability. In a

Example of not exists in sql, Example of NOT EXISTS in SQL Example: Us...

Example of NOT EXISTS in SQL Example: Use of NOT EXISTS CREATE ASSERTION Must_be_enrolled_to_take_exam_alternative1 CHECK ( NOT EXISTS (SELECT StudentId, CourseId

produce vertical output format- oracle, Create a Oracle procedure to produ...

Create a Oracle procedure to produce vertical output format when selecting rows from a database table.

Using rename in combination with join - sql, Using RENAME in combination wi...

Using RENAME in combination with JOIN - SQL Example gives pairs of ids of students having the same name, by joining two renamings of IS_CALLED. Example gives an equivalent ex

Keyword and parameter description - goto statement, Keyword and Parameter D...

Keyword and Parameter Description: label_name: This is an undeclared identifier which labels an executable statement or the PL/SQL block. You can use a GOTO statement to

Merge two data sources to sql azure database, Project Description: I hav...

Project Description: I have two types of data sources. One that is a list in SharePoint and another that is an access desktop database. The access desktop database is fairly com

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