Tuesday, September 27, 2016
TSQLCircleLive: What will be the result of the query below? Explai...
TSQLCircleLive: What will be the result of the query below? Explai...: select case when null = null then 'True' else 'False' end as ResultSet;
Monday, September 26, 2016
What will be the result of the query below? Explain your answer?
select case when null = null then 'True' else 'False' end as ResultSet;
Different types of ANSI standard joins
INNER JOIN (SIMPLE JOIN): This returns all rows for which
there is at least one match in BOTH tables. This is the default type of join if
no specific JOIN type is specified.
LEFT JOIN (or
LEFT OUTER JOIN): This returns all rows from the left table,
and the matched rows from the right table; i.e., the results will contain all
records from the left table, even if the JOIN condition doesn’t find any
matching records in the right table. This means that if the ON clause doesn’t
match any records in the right table, the JOIN will still return a row in the
result for that record in the left table, but with NULL in each column from the
right table.
RIGHT JOIN (or
RIGHT OUTER JOIN): This returns all rows from the right
table, and the matched rows from the left table. This is the exact opposite of
a LEFT JOIN. The results will contain all records from the right table, even if
the JOIN condition doesn’t find any matching records in the left table. This
means that if the ON clause doesn’t match any records in the left table, the
JOIN will still return a row in the result for that record in the right table,
but with NULL in each column from the left table.
FULL JOIN (or
FULL OUTER JOIN): This
returns all rows for which there is a match in EITHER of the tables. Theoretically,
a FULL JOIN associations the effect of applying both a LEFT JOIN and a RIGHT
JOIN; i.e., its result set is equivalent to performing a UNION of the results
of left and right outer queries.
CROSS JOIN: This returns all records where each row from the first table is
combined with each row from the second table (i.e., returns the Cartesian
product of the sets of rows from the joined tables). Note that a CROSS JOIN can
either be specified using the CROSS JOIN syntax (“explicit join notation”) or
(b) listing the tables in the FROM clause separated by commas without using a
WHERE clause to supply join criteria (“implicit join notation”).
What does UNION do or what result set UNION return ? What is the difference between UNION and UNION ALL?
UNION merges the contents of two structurally-compatible tables into a single combined table. This return unique records from all the table.
The difference between UNION and UNION ALL is that UNION will omit duplicate records whereas UNION ALL will include duplicate records.
It is important to note that the performance of UNION ALL will typically be better than UNION , since UNION requires the server to do the additional task of removal of any duplicates. So, in cases where is is certain that there will not be any duplicates, or where having duplicates is not a problem, use of UNION ALL would be recommended for performance reasons.
Sunday, September 4, 2016
SPACE
USE CASE: When
we are working on TSQL or some text based scripting we may need some extra
space in between two string, we can add space without space function as well
but it is possible we may miss to put space in between string or text. If we have to
maintain fixed length of space in between string every time we can use TSQL
SPACE function.
SPACE
Returns
a string of repeated spaces.
SYNTAX
SPACE
( integer_expression )
ARGUMENTS
integer_expression
Is a positive integer that indicates the number of spaces. If integer_expression is negative, a null string is returned.
Is a positive integer that indicates the number of spaces. If integer_expression is negative, a null string is returned.
REMARKS
To include spaces in
Unicode data, or to return more than 8000 character spaces, use REPLICATE
instead of SPACE.
EXAMPLE:-
/*
Considering employee table
has three rows;
ID
|
FIRSTNAME
|
LASTNAME
|
1
|
PRIYA
|
KUMARI
|
2
|
AYUSH
|
KUMAR
|
3
|
RICHA
|
SINGH
|
*/
SELECT FirstName +’,’+SPACE(3)+LastName
from employee;
PRIYA KUMARI
AYUSH KUMAR
RICHA SINGH
Labels:
SQL BASICS,
TSQL
Location:
Noida, Uttar Pradesh 201301, India
STUFF
Use case when
we are working on TSQL or Query ,some time we came such a situation when we need to
replace a set of string into another set of string, this can be anything like
string, character, numeric or special character. We can handle such situation
using STUFF function.
STUFF
The STUFF function inserts a string into another string. It deletes a specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position.
Syntax:-
STUFF (Character Expression, Start, Length, Replace with
Expression)
Arguments: This
function uses the following parameters.
Character Expression: Is an expression of character data. Character
Expression can be a constant, variable, or column of either character or binary
data.
Start: Is an integer value
that specifies the location to start deletion and insertion. If start or length
is negative, a null string is returned. If start is longer than the first
Character Expression, a null string is returned. Start can be of type bigint.
Length: Is an integer that
specifies the number of characters to delete. If length is longer than the
first Character Expression, deletion occurs up to the last character in the last Character
Expression. Length can be of type bigint.
Replace With Expression: Is an expression of character data. Replace with Expression can
be a constant, variable, or column of either character or binary data. This
expression will replace length characters of Character
Expression beginning at start.
Return Types
Returns character data
if character expression is one of the supported character data types.
Returns binary data if character expression is one of the supported
binary data types.
Remarks
If the start position
or the length is negative, or if the starting position is larger than length of
the first string, a null string is returned. If the start position is 0, a null
value is returned. If the length to delete is longer than the first string, it
is deleted to the first character in the first string.
An error is raised if the resulting value is
larger than the maximum supported by the return type.
EXAMPLE
SELECT
STUFF(‘abcdefgh’, 2, 3, 'xyzwsq');
GO
OUTPUT
-------------------
axyzwsqbcdefgh
(1
row(s) affected)
Subscribe to:
Posts (Atom)