SQL JOIN commands

• Relational Database Management Systems (RDBMS) use Structured Query Language (SQL) Join commands to join tables.
• Join commands can combine rows from two or more tables.
• Exact values are produced using conditional parameters added with the WHERE clause.

SQL Join

• A JOIN clause combines rows from two or more tables based on a related column.
• JOIN conditions can be specified using either FROM or WHERE clauses.
• FROM clause is recommended for specifying JOIN conditions.
• WHERE and HAVING clauses can contain search conditions to further filter rows.

Types of Joins

• Joins are categorized as inner joins and outer joins.

Inner Join

• The typical join uses a comparison operator (= or <>) to match rows from two tables.
• Matching is based on values in common columns.

Outer Join

• Outer joins can be left, right, or full outer joins.
• They are specified with keywords in the FROM clause.

Left Join (Left Outer Join)

• The result set includes all rows from the left table specified in the left outer clause.
• If a row in the left table has no matching rows in the right table, the result set contains null values for select list columns from the right table.

Right Join (Right Outer Join)

• A right outer join is the reverse of a left outer join.
• All rows from the right table are returned.
• Null values are returned for the left table when a right table row has no matching row in the left table.

Full Join (Full Outer Join)

• A full outer join returns all rows in both the left and right tables.
• If a row has no match in the other table, the select list columns from the other table contain null values.
• When tables match, the result set row contains data values from the base tables.

Cross Join

• Cross join returns all rows from the left table, combined with all rows from the right table.
• Cross joins are also called Cartesian products.
• Cartesian products multiply the value of one table to the joined table.

Key Understanding:

• Extracting records relies on a common column (key) that joins tables.
• Joins enable connections between tables using a primary key.

Creating a View

• Right-click the View folder, then select New View to create a view.
• The View design is divided into four panes: diagram, criteria, SQL query, and results.
• The diagram shows column names of each table; commands are generated by connecting tables.
• The SQL query pane allows coding commands, automatically generating SQL commands when the diagram pane is triggered.

Operators in the WHERE Clause

• An operator is a symbol or reserved word that specifies an action, evaluating the expression as true or false.

Conditional Operators

• Conditional operators are also called comparison operators.
• They evaluate whether expressions yield a true or false value.
• They can be used in all expressions except those with text, next, or image data types.

Logical Operators

• Logical operators connect two expressions and test their conditional values.

• Like comparison operators, they have a Boolean data type with a value of true or false

• The AND operator displays records when all conditions are TRUE.

• The OR operator displays records that meet either or both conditions.

SQL LIKE Operator & Wildcard Characters

• The SQL LIKE operator compares a value to similar values using wildcard operators.
• It allows matching searches and returns a Boolean type (true/1 or false/0) value for a character string data type.

Wildcard Characters

• Percent Sign (%): Represents a sequence of zero, one, or multiple characters.
• Underscore (_): Represents a single number or character.
• [charlist]: Represents a single character within a character list.
• [^charlist]: Represents any single character NOT in the list.

Key Terms

• Column list: List of column names in a table.
• Table name: The name of the table to be used in the SELECT command.
• WHERE: A keyword in a SELECT command that tests whether an expression is true or false.
• LIKE: A clause which replaces the comparison operator.
• Pattern: A string of up to 8,000 characters to search for, provided by a wildcard character.
• SQL LIKE Operator: A powerful filter for thorough searches, but may expose or produce garbage results.
• Ending Character String: Place the percent sign (%) at the end of the character string to display.
• Beginning Character String: Place the percent sign (%) at the beginning of the character string to locate.
• ORDER BY Command: Sorts the list.
• !=: means not equal.
• %: Represents one or more sequences of characters.
• [ ]: Wildcard operator representing a character list.
• [^]: Wildcard operator representing anything other than a character list.
• < > =: Means not equal.
• Between Clause: Filters data within a given range.
• Conditional Operator: Evaluates a Boolean expression to either true or false.
• In Clause: Filters data within the given range.
• Like Clause: Compares a value similar to a specified one with a wildcard character.
• Logical Operator: Combines two or more expressions.
• Not In Clause: Filters data not within the given range.
• Underscore (_): Denotes a single character in the Like operator used in the Where clause.
• Where Clause: Filters data based on the given expression.