SQL Conditional Join

3 Key excerpts on "SQL Conditional Join"

• 

  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
• 

  eBook - ePub

  PROC SQL

  Beyond the Basics Using SAS, Third Edition

  • Kirk Paul Lafler(Author)
  • 2019(Publication Date)
  • SAS Institute

    (Publisher)

  This chapter discusses a number of join topics including why joins are important, the differences between the various join techniques, the importance of the WHERE clause in creating joins, creating and using table aliases, joining three or more tables of data, outer (left, right, and full) joins, subqueries, and set operations. It is important to recognize that many of these techniques can be accomplished using DATA step programming techniques, but the simplicity and flexibility found in the SQL procedure makes it especially useful, if not indispensable, as a tool for the practitioner.

  Why Joins Are Important

  As relational database systems continue to grow in popularity, the need to access normalized data stored in separate tables becomes increasingly important. By relating matching values in key columns in one table with key columns in the other table(s), you can retrieve information as if the data were stored in one huge file. The results can provide new and exciting insights into possible data relationships.

  Information Retrieval Based on Relationships

  Being able to define relationships between multiple tables and retrieve information based on these relationships is a powerful feature of the relational model. A join of two or more tables provides a means of gathering and manipulating data in a single SELECT statement. You join two or more tables by specifying the table names in a SELECT statement. Joins are specified on a minimum of two tables at a time, where a column from each table is used for the purpose of connecting the two tables. Connecting columns should have “like” values and the same column attributes because the join’s success is dependent on these values.

  In a typical join, you name the relevant columns in the SELECT statement, specify the tables to be joined in the FROM clause, and in the WHERE clause you specify the relationship that you want revealed. That is, you describe the data subset that you want to produce. To be of use (and of a manageable size), your join needs a WHERE clause to constrain the results and ensure their utility and relevance.

  Note:  When you create a join without a WHERE clause, you are creating an internal, virtual table called a Cartesian product

  Sign up to read

  Learn more about book