SQL Conditional Join

3 Key excerpts on "SQL Conditional Join"

• 

  eBook - PDF

  Information Modeling and Relational Databases

  From Conceptual Analysis to Logical Design

  • Terry Halpin(Author)
  • 2001(Publication Date)
  • Morgan Kaufmann

    (Publisher)

  Cross joins are fairly rare in practice. Much more common are conditional joins. The new syntax includes the join operator in the from clause and the join condition in an on clause. Figure 11.42 shows a relational schema, sample population, and a condi-tional join query to retrieve the name of each female employee and their department. To aid readability, it’s a good idea to indent the on clause, as shown. Each column men-tioned in the query occurs in just one of the tables, so there is no need to qualify the column names with the table names. In this case, the join takes place along the foreign key reference shown in the figure. Although joins often involve foreign key references, they don’t have to. All we need is that the columns being joined are based on the same domain. The same query may be formulated in SQL-89 as follows: select empName, deptName from Employee, Department where dept = deptCode and sex = ‘F’ 11.6 SQL: Joins 503 504 Chapter 11: Relational Languages Table 11.11 Joins in SQL-92/SQL:1999 and SQL-89. Join type New syntax in SQL-92, SQL:1999 SQL-89 syntax cross select * from A cross join B select * from A, B conditional select * from A join B on condition select * from A, B where condition column-list select * from A join B using (c 1 , ...) --c 1 , ... are unqualified select A.c 1 , ... , ... --omit B.c 1 , ... from A, B where A.c 1 = B.c 1 and ... --join columns are qualified natural inner select * from A natural [ inner ] join B --join column in result is c select A.c,... --omit B.c from A, B where A.c = B.c --join column in result is A.c left outer select * from A natural left [ outer ] join B --join cols are unqualified; --nulls generated for nonmatches --to join on fewer cols use: A left [ outer ] join B using (c 1 , ...) --to join cols with different names: A left [ outer ] join B on condition } select A.c,... { omit B.c } from A, B where A.c = B.c union all select c, ..., ‘ ? ’, ...

  Sign up to read

  Learn more about book
• 

  eBook - ePub

  A Comprehensive Study of SQL

  Practice and Implementation

  • Jagdish Chandra Patni(Author)
  • 2022(Publication Date)
  • CRC Press

    (Publisher)

  6 Conditional Statements and Operators in SQL

  DOI: 10.1201/9781003324690-6

  Conditional statements allow us to change the way our program behaves based on the input it receives, the contents of variables, or several other factors.

  6.1 Introduction

  A database encompasses one or multiple tables with rows and columns. Each table is represented by a name, and SQL statements are used to perform any action on a database. Here, we will discuss conditional statements in SQL, which are based on some conditional expressions. An expression can be any arrangement of SQL literals, like variables, operators, and functions that, on execution, returns a logical value if the condition is satisfied.

  Although SQL isn’t a general-purpose language, it includes conditional statements with a similar structure to other languages. The classic IF and CASE statements permit the ability to change data on the query level instead of modifying them in another environment, such as a worksheet or a data frame.

  6.2 Conditional Evaluation

  While neither of these statements is hard to master, especially with previous exposure to programming languages, they offer plenty of power and flexibility to modify query results. To demonstrate how these statements may be used, a simple table will be created detailing a student ID, name, department, and project.

  Create table student ( studentID Integer Primary Key, name varchar (10), department varchar(10), project char(10) );

  6.3 Types of Condition

  6.3.1 IF Condition

  The most basic form of an IF statement in SQL looks very similar to conditional statements in most other programming languages and software.

  • If (condition, True, False) from table;

  An IF statement simply introduces some condition and then returns a result based on whether the condition is true or false. When the condition is true, the second parameter is returned, and when false, the third parameter.

  Sign up to read

  Learn more about book
• 

  eBook - ePub

  Introductory Relational Database Design for Business, with Microsoft Access

  • Jonathan Eckstein, Bonnie R. Schultz(Authors)
  • 2017(Publication Date)
  • Wiley

    (Publisher)

  State field value does not match the condition. When comparing text fields to literal character strings such as “CA”, you should enclose the literal character strings in double quotes. Otherwise, SQL will try to interpret the character string as an attribute name.

  Inner Joins

  So far, this chapter has considered only queries drawn from a single table. We now discuss how SQL can express queries based on data from multiple tables. The most common technique for basing queries on multiple tables is called an inner join. An inner join consists of all combinations of rows selected from two tables that meet some matching condition, formally called a join predicate. One standard syntax for an inner join, of which we have already seen examples earlier in this book, is:

  First_Table INNER JOIN Second_Table ON Condition

  Formally, this syntax specifies that the query should form a table consisting of all combinations of a record from First_Table with a record from Second_Table for which Condition evaluates to “true.” Most frequently, Condition specifies that a foreign key in one table should match a primary key in the other.

  Here is an example of an inner join based on the plumbing store database:

  SELECT FirstName, LastName, OrderDate FROM CUSTOMER INNER JOIN ORDERS ON CUSTOMER.CustomerID = ORDERS.CustomerID;

  The INNER JOIN expression is now the data_source following the FROM keyword, where before we used a single table. This construction means that the data to be displayed is taken from the temporary table resulting from the inner join operation. The particular inner join expression, namely,

  CUSTOMER INNER JOIN ORDERS ON CUSTOMER.CustomerID = ORDERS.CustomerID

  specifies that the query should be based on all combinations of records from the CUSTOMER and ORDERS tables that have matching CustomerID fields. This kind of primary key to foreign key matching condition is by far the most common kind of inner join. CUSTOMER.CustomerID refers to the CustomerID field from the CUSTOMER table, while ORDERS.CustomerID refers to the CustomerID field from the ORDERS table. The use of “. ” here is called qualification. It is required to eliminate ambiguity whenever several underlying tables have fields of the same name, as is the case for the CustomerID in this example: if we were to just write CustomerID , SQL would not be able to tell whether we were referring to the CustomerID field in the CUSTOMER table or the CustomerID field in the ORDERS table. To resolve this ambiguity, we preface an attribute name with a table name and “. ”: for example, CUSTOMER.CustomerID means the CustomerID

  Sign up to read

  Learn more about book