SQL Joins, Clauses, and Functions: A Comprehensive Look
Inner Join
An INNER JOIN, also called a Simple Join, is the simplest table join. It is the default link type to join tables. You may even omit INNER to leave only JOIN. When you see JOIN without any words in front, it means INNER JOIN. In order to distinguish other types of JOIN, omission is not encouraged unless your query length is an issue that requires you to reduce your query to the minimum size. An INNER JOIN syntax looks like the following:
SELECT T[0]|[1].column_name(s) FROM table_name0 T0 INNER JOIN table_name1 T1 ON T0.column_name=T1.column_name
Outer Join
Left Outer Join
A LEFT JOIN is very useful when you need to display all data records from one table but also want to know some secondary table data without restricting the query results.
Right Outer Join
A RIGHT JOIN means all records in the right table will be returned, regardless of the left table linking condition. If the match cannot be found in the T0 table, it simply returns a Null value for any columns coming from the T0 table.
A RIGHT JOIN clause syntax looks like the following:
SELECT T[0]|[1].column_name(s) FROM table_name0 T0 RIGHT JOIN table_name1 T1 ON T0.column_name=T1.column_name
Full Outer Join
A Full Outer Join syntax looks like the following:
SELECT T[0]|[1].column_name(s) FROM table_name0 T0 FULL OUTER JOIN table_name1 T1 ON T0.column_name=T1.column_name
A Full Outer Join will return all rows from both tables, regardless of matching conditions.
Self-Join
A Self-Join is a special join in which a table is joined to itself. Self-Joins are used to compare values in a column with other values in the same column in the same table. It can be used for certain special needs, such as obtaining running counts or running totals in a SQL query. It is often used in subqueries.
WHERE — Query Conditions
If the WHERE clause exists in a query, it always follows the FROM clause. Its syntax is as follows:
SELECT column_name(s) FROM table_name(s) WHERE [(]expression operator expression [and/or] [expression operator expression ][)]
BETWEEN — Defining Ranges
A BETWEEN operator is used to specify a range to test. The syntax for a BETWEEN operator is:
Value1 [ NOT ] BETWEEN Value2 AND Value3
All arguments are discussed as follows:
- Value1 is the value to be tested in the range defined by Value2 and Value3.
- NOT specifies that the result of the predicate be negated. It is optional.
- Value1 is any valid value with the same data type as Values.
- Value3 is any valid value that is greater than Value2 with the same data type.
- AND is mandatory and acts as a placeholder that indicates Value1 should be within the range indicated by Value2 and Value3.
- This clause is equivalent to Value1 >= Value2 and Value1 <= Value3.
For example, if you have a query like this:
WHERE t1.RefDate >= [%0] and t1.RefDate <= [%1]
It is equivalent to the following:
WHERE t1.RefDate BETWEEN [%0] and [%1]
IN/EXISTS — Value List for Conditions
IN or NOT IN is an operator to compare a value with an existing value list that has more than one value. You are allowed to have only one value in the list. However, that should be by equal operator.
LIKE — Finding Similar Records
A LIKE operator allows you to do a search based on a pattern rather than specifying exactly what is desired (as in IN) or spelling out a range (as in BETWEEN). LIKE determines whether a value to be tested matches a specified pattern. A pattern can include wildcard characters. During this matching, wildcard characters play flexible roles to allow partly unmatched values to go through.
A LIKE operator syntax is as follows:
Value [ NOT ] LIKE Pattern
Two arguments are as follows:
- Value is any valid value of character string data type.
- Pattern is the specific string of characters to search for in the Value and can include the following valid wildcard characters. The pattern can be a maximum of 8,000 bytes.
GROUP BY — Summarizing Data
A GROUP BY clause is very useful if you need to aggregate your data based on certain columns. It is optional and must follow the FROM and WHERE clauses. If you remember the first query before discussing statements, you have: GROUP BY T0.ShortName
The query script is simple:
SELECT T0.CardCode AS ‘BP Code’, T0.CardName AS ‘BP Named’, SUM(T0.DocTotal) AS ‘Total’ FROM dbo.OINV T0 WHERE T0.CardCode <> ”
HAVING — Summary Report Conditions
A HAVING clause is normally used with a GROUP BY clause. This clause is optional. It is equivalent to a WHERE clause under the main query body. It specifies that a SELECT statement should only return rows where aggregate values meet the specified conditions.
If you remember the first query before discussing, you have:
HAVING SUM(ISNULL(T0.Debit,0) – ISNULL(T0.Credit,0)) > 0
ORDER BY — Sorting Report Results
An ORDER BY clause is very simple when you need to sort your query result based on certain columns. This clause is always the last clause to be used in the query. If you have UNION or UNION ALL to combine more than one query, this clause may only be added to the end of the entire query.
There are two types of orders: ascending and descending. Descending can be abbreviated to DESC in the end. Ascending can be abbreviated to ASC. If DESC is not included, the default ORDER BY will be ascending. Since ascending is the default order, it is usually omitted from the query.
ORDER BY SUM(ISNULL(T0.Debit,0) – ISNULL(T0.Credit,0)) DESC
UNION/UNION ALL — Combining Queries
The UNION clause combines the results of two or more SQL queries into one query result set. To use this clause, the number and order of columns from those queries must be the same with compatible data types. Any duplicate records are automatically removed by the UNION clause. It works like DISTINCT.
Important Functions
ISNULL() Predicate
An ISNULL() function is used for replacing Null with the specified replacement value. The syntax for the function is as follows:
ISNULL (Value1,Value2)
Value1 is normally a column value or a variable to be checked. Value2 is a fixed value to be replaced.
SUM() Function
A SUM() function is used for returning the summed total of all the values, or only the DISTINCT values, from the numeric columns. The syntax for the function is as follows:
SUM ([ ALL | DISTINCT ] value)
Both ALL and DISTINCT keywords are optional. If you add the DISTINCT keyword to the function, it will only return the sum of distinct values. ALL is the default selection. There is usually no need to add to the function at all. The value here can be a constant, column, or a function, and any combination of arithmetic operators.
MAX() Function
A MAX() function is used for returning the maximum value from all checked values. The syntax for the function is as follows:
MAX ([ ALL | DISTINCT ] value)
Both ALL and DISTINCT keywords are optional. If you add a DISTINCT keyword to the function, it will only return the maximum of the distinct values. The result will be exactly the same as the one without this keyword. ALL is the default selection. There is usually no need to add to the function at all. The value here can be a constant, column, or function, and any combination of arithmetic, bitwise, and string operators. The MAX function can be used with numeric, character, and datetime columns. An example query would be as follows:
SELECT T0.CardCode, MAX(T0.DocTotal) ‘Total’ FROM dbo.OINV T0 WHERE T0.CardCode LIKE ‘[%0]%’ GROUP BY T0.CardCode ORDER BY MAX(T0.DocTotal) DESC
With this query, the maximum A/R invoice total will be returned in a range of customers or all customers if you input nothing when you run the query.
MIN() Function
A MIN() function is used for returning the minimum value from all checked values. The syntax for the function is as follows:
MIN ([ ALL | DISTINCT ] Value)
Both ALL and DISTINCT keywords are optional. If you add the DISTINCT keyword to the function, it will only return the minimum of the distinct values. The result will be exactly the same as the one without this keyword. ALL is the default selection. There is usually no need to add to the function at all. The value here can be a constant, column, or function, and any combination of arithmetic, bitwise, and string operators. MIN can be used with numeric, character, and datetime columns. An example query would be as follows:
SELECT T0.CardCode, MIN(T0.DocTotal) ‘Total’ FROM dbo.OPCH T0 WHERE T0.CardCode LIKE ‘[%0]%’ GROUP BY T0.CardCode ORDER BY MIN (T0.DocTotal)
With this query, the minimum A/P invoice total will be returned in a range of vendors or all vendors if you input nothing when you run the query.
COUNT() Function
A COUNT() function returns the number of items in a group. The syntax for the function is as follows: COUNT { [ [ ALL | DISTINCT ] Value ] | * })
Both ALL and DISTINCT keywords are optional. If you add the DISTINCT keyword to the function, it will only return the count of the distinct values. ALL is the default selection. There is usually no need to add to the function at all. The value here can be of any type except text or image. Aggregate functions and subqueries are not permitted.
Another useful query example is as follows: SELECT Count(*) FROM JDT1. You will get the number of records for one of your largest tables.
DATEDIFF() Function
A DATEDIFF() function returns an integer number of intervals of a specified type between two dates. The syntax is simple:
DATEDIFF ( Datepart , Date1 , Date2 )
Three arguments are:
- Datepart is the parameter that specifies on which type of the date or time to calculate the difference. The Datepart and abbreviations can be found from the following table. These Datepart and abbreviations are an exclusive list that cannot be supplied as a user-declared variable.
- Date1 is the starting date for the interval. It is an expression that returns a datetime value or a character string in a date format.
- Date2 is the ending date for the interval. It is also an expression that returns a datetime value or a character string in a date format.
Date and time values included in the function must be within a valid range. Years must be less than or equal to 9999. Months must be between 1 and 12. Days must be between 1 and 31. Hours: 0 through 23. Minutes: 0 through 59. Seconds: 0 through 59.
DATEADD() Function
A DATEADD() function returns a new datetime value based on adding or subtracting an interval to a specified date. The syntax is as follows:
DATEADD (Datepart , Number, Date )
Three arguments are as follows:
- Datepart is the parameter that specifies the part of the date or time to return. The lists of the Dateparts and abbreviations can be found above, under the DATEDIFF() function.
- Number is the value used to increment Datepart. If you specify a value that is not an integer, the decimal part of the value is truncated. For example, if you specify day for Datepart and 3.68 for the number, the date is incremented only by 3.
- Date is an expression that returns a datetime value or a character string in a date format. When you enter datetime values, always enclose them in quotation marks.
An example query is as follows:
SELECT DateAdd(mm, 5, ’05-05-2011′)
It will return 10-05-2011 if your date format is mm-dd-yyyy, or it returns 05-10-2011 if your date format is dd-mm-yyyy.
DATEPART() Function
A DATEPART() function returns an integer that represents the specified Datepart of the specified date or time. The syntax is very simple:
DATEPART ( Datepart , Date )
Two arguments are as follows:
- Datepart is the parameter that specifies the part of the date or time to return. The lists of the Dateparts and abbreviations can be found above, under the DATEDIFF() function.
- Date is an expression that returns a datetime value or a character string in a date format. When you enter datetime values, always enclose them in quotation marks.
An example query would be as follows:
SELECT DatePart(WW, ’05-05-2011′)
This would return 19 since the date May 5th, 2011, belongs to the nineteenth week of 2011.
CAST()/CONVERT() Function
To convert an expression of one data type to another explicitly, CAST and CONVERT can be used. They provide similar functionality with different syntaxes.
Syntax for CAST:
CAST ( Value AS Data_type [ (Length ) ])
Syntax for CONVERT:
CONVERT ( Data_type [ ( Length ) ] , Value [ , Style ] )
Here, Value is any valid expression.
Data_type is the target system-supplied data type such as integer data, character data, monetary data, date and time data, binary strings, and so on. Alias data types cannot be used with this function.
Length is an optional parameter of string data types. For CONVERT, if the length is not specified, the default length is 30 characters.
Style is the style of the value to be returned. When style is Null, the result returned is also Null. In other words, you have to define style in the CONVERT function; otherwise, you may get nothing.
CASE Expressions
A CASE expression is a unique conditional statement providing if/then/else logic for any ordinary SQL statement. It returns a single value from one of the multiple possible result expressions by evaluating a list of conditions.
IF Expressions
An IF expression introduces a condition that determines whether the next statement is executed. The optional ELSE expression can give an alternate action to be executed when the IF condition is not satisfied.
Similar to CASE expression, the syntax for IF expression is better shown with a query example:
If Exists(SELECT T1.DocEntry FROM OIGE T0 INNER JOIN IGE1 T1 ON T0.DocEntry=T1.DocEntry WHERE T0.DocEntry = @list_of_cols_val_tab_del Group By T1.DocEntry Having Sum(T1.Quantity) < 0)
In the query example, the IF expression is used to check whether the query after EXISTS returns anything. If it is positive, this query could be used for effectively blocking the Goods Receipt being created. Notice that the parentheses are needed when there is a SELECT statement included in the query body.