SQL "select where not in subquery" returns no results

 SQL "select where not in subquery" returns no results

 

These articles in my blog describe the differences between the methods in more detail:

• NOT IN vs. NOT EXISTS vs. LEFT JOIN / IS NULL: SQL Server
• NOT IN vs. NOT EXISTS vs. LEFT JOIN / IS NULL: PostgreSQL
• NOT IN vs. NOT EXISTS vs. LEFT JOIN / IS NULL: Oracle
• NOT IN vs. NOT EXISTS vs. LEFT JOIN / IS NULL: MySQL

---

There are three ways to do such a query:

• LEFT JOIN / IS NULL:

  SELECT  *
  FROM    common
  LEFT JOIN
          table1 t1
  ON      t1.common_id = common.common_id
  WHERE   t1.common_id IS NULL
  

• NOT EXISTS:

  SELECT  *
  FROM    common
  WHERE   NOT EXISTS
          (
          SELECT  NULL
          FROM    table1 t1
          WHERE   t1.common_id = common.common_id
          )
  

• NOT IN:

  SELECT  *
  FROM    common
  WHERE   common_id NOT IN
          (
          SELECT  common_id
          FROM    table1 t1
          )
  

When table1.common_id is not nullable, all these queries are semantically the same.

When it is nullable, NOT IN is different, since IN (and, therefore, NOT IN) return NULL when a value does not match anything in a list containing a NULL.

This may be confusing but may become more obvious if we recall the alternate syntax for this:

common_id = ANY
(
SELECT  common_id
FROM    table1 t1
)

The result of this condition is a boolean product of all comparisons within the list. Of course, a single NULL value yields the NULL result which renders the whole result NULL too.

We never cannot say definitely that common_id is not equal to anything from this list, since at least one of the values is NULL.

Suppose we have these data:

common

--
1
3

table1

--
NULL
1
2

LEFT JOIN / IS NULL and NOT EXISTS will return 3, NOT IN will return nothing (since it will always evaluate to either FALSE or NULL).

In MySQL, in case on non-nullable column, LEFT JOIN / IS NULL and NOT IN are a little bit (several percent) more efficient than NOT EXISTS. If the column is nullable, NOT EXISTS is the most efficient (again, not much).

In Oracle, all three queries yield same plans (an ANTI JOIN).

In SQL Server, NOT IN / NOT EXISTS are more efficient, since LEFT JOIN / IS NULL cannot be optimized to an ANTI JOIN by its optimizer.

In PostgreSQL, LEFT JOIN / IS NULL and NOT EXISTS are more efficient than NOT IN, sine they are optimized to an Anti Join, while NOT IN uses hashed subplan (or even a plain subplan if the subquery is too large to hash)

 

concat the output of the subquery?

concat the output of the subquery?

select group_concat(id) as video_list
from videos 
where duration=0

 

Export array (table) into csv-file in Powershell

Export array (table) into csv-file in Powershell

 First of all, we need to define the array:

1	$export_array = @();

Then, fill in the data you need:

1 2 3 4

$export_array += ,@(""); # just an empty string $export_array += ,@($a1, $b1, $c1); $export_array += ,@($a2, $b2, $c2); $export_array += ,@($a3, $b3, $c3);

Compose csv file name and path to it:

1 2 3

$datetime = Get-Date -Format "yyyy.MM.dd_HH-mm-ss"; $file_name = "audit_result_" + $datetime + ".csv"; $file_path = "./" + $file_name;

Actual export of array into csv-file:

1 2 3 4 5 6 7 8 9

foreach($item1 in $export_array)  {    $csv_string = "";   foreach($item in $item1)   {     $csv_string = $csv_string + $item + ";";   }   Add-Content $file_path $csv_string; }

In powershell how to output an ArrayList to CSV

 In powershell how to output an ArrayList to CSV

 

$CSVArrayList = new-Object System.Collections.ArrayList

[void]$CSVArrayList.Add(@('1','2','3'))
[void]$CSVArrayList.Add(@('4','5','6'))

Set-Content "./Output.csv" -Value $null

Foreach ($arr in $CSVArrayList) {
      $arr -join ',' | Add-Content "./Output.csv"
}

 

The STRING_SPLIT function in SQL Server

 The STRING_SPLIT function in SQL Server

 

Syntax:

The syntax is very simple as this table valued built-in function takes only two parameters. First one is a string and the second one is a single character.

STRING_SPLIT (string, separator)

The following sample shows simplest usage of this function.

select value from STRING_SPLIT('apple,banana,lemon,kiwi,orange,coconut',',')

The following SELECT query will return an error because of the database compatibility level.

ALTER DATABASE AdventureWorks2012 SET compatibility_LEVEL=120 GO select value from STRING_SPLIT('apple , banana , lemon , kiwi , orange ,coconut',',')

The reason for this error is that we decreased the database compatibility level under the 130 and SQL Server returns an error. Keep in mind, that if you are planning to use this function in your customer environment you have to be sure about their database compatibility level.

STRING_SPLIT and WHERE clause:

Through the WHERE clause, we can filter the result set of the STRING_SPLIT function. In the following select statement, the WHERE clause will filter the result set of the function and will only return the row or rows which start with “le”

select value from STRING_SPLIT('apple,banana,lemon,kiwi,orange,coconut',',') WHERE value LIKE 'le%'

Also, we can use the function in this form:

USE AdventureWorks2014 GO   select * from HumanResources.Employee WHERE [jobtitle] IN (select value from string_split('Chief Executive Officer , Design Engineer',','))

Now, we will look at the execution plan with help of ApexSQL Plan. We can see the Table valued function operator in the execution plan.

When we hover over the table-valued function operator in the execution plan, we can find out all the details about this operator. Under the object label, the STRING_SPLIT function can be seen. These all details tell us that this function is a table-valued function.

STRING_SPLIT and ORDER BY

Another requirement which we need in the SELECT statements is sorting functionality. We can sort the output of this function which looks like the other T-SQL statements.

select value from STRING_SPLIT('apple,banana,lemon,kiwi,orange,coconut',',') order by value

Note: When I reviewed some customer feedback about SQL Server, I came across a suggestion about the STRING_SPLIT function which is “The new string splitter function in SQL Server 2016 is a good addition but it needs an extra column, a ListOrder column which denotes the order of the split values.” In my thought, this feature can be very useful for this function and I voted for this suggestion.

STRING_SPLIT and JOIN:

We can combine the function result set to the other table with the JOIN clause.

USE [AdventureWorks2014] GO   SELECT       [PersonType]       ,[NameStyle]      ,[FirstName]       ,[MiddleName]       ,[LastName]    FROM [Person].[Person] P INNER JOIN string_split('Ken,Terri,Gail',',') on P.FirstName=value

Also, we can use CROSS APPLY function to combine the STRING_SPLIT function result set with other tables. CROSS APPLY function provides us to join table value function output to other tables.

In the following sample, we will create two tables and first table (#Countries) stores name and the continent of countries and second table (#CityList) stores city of countries table but the crucial point is #CityList table stores the city names as a string array which is separated by a comma. We will join this to the table over country columns and use the CROSS APPLY function to transform city array into a column. The below image can illustrate what will we do.

DROP TABLE IF EXISTS #Countries GO DROP TABLE IF EXISTS #CityList GO CREATE TABLE #Countries (Continent VARCHAR(100), Country VARCHAR(100)) GO CREATE TABLE #CityList (Country VARCHAR(100), City VARCHAR(5000)) GO INSERT INTO  #Countries VALUES('Europe','France'),('Europe','Germany')   INSERT INTO #CityList VALUES('France','Paris,Marsilya,Lyon,Lille,Nice'), ('Germany','Berlin,Hamburg,Munih,Frankfurt,Koln')   SELECT CN.Continent,CN.Country,value FROM #CityList CL CROSS APPLY string_split(CL.City,',')  INNER JOIN #Countries CN ON  CL.Country = CN.Country     DROP TABLE IF EXISTS #Countries GO DROP TABLE IF EXISTS #CityList GO

More details about STRING_SPLIT

After all the basic usage methodology of this function; we will delve into more detail. In the previous examples we always used a comma (,) as a separator for the function, however, we may need to use other symbols as a separator. The STRING_SPLIT function allows us to use other symbols as a separator, but it has one limitation about this usage. According to the MSDN; separator is a single data type and this parameter data types can be nvarchar (1), char (1), and varchar (1). Now, we will make a sample about it. The following SELECT statement will run without error. As well as we will use at (@) instead of a comma (,).

DECLARE @STRINGLIST AS VARCHAR(1000)='apple,banana,lemon,kiwi,orange,coconut' DECLARE @SEPERATOR VARCHAR(1)=',' select * from STRING_SPLIT(@STRINGLIST,@SEPERATOR)

However, the following SELECT statement will return an error because of the data type declaration.

DECLARE @STRINGLIST AS VARCHAR(1000)='apple@+@banana@+@lemon@+@kiwi@+@orange@+@coconut' DECLARE @SEPERATOR VARCHAR(3)='@+@' select * from STRING_SPLIT(@STRINGLIST,@SEPERATOR)

“Procedure expects parameter ‘separator’ of type ‘nchar(1)/nvarchar(1)’”. The definition of error is very clear and it indicates a problem that is related to the data type of separator. After this sample, a question can appear in your mind. Can we assign NULL value to separator? We will test and learn.

DECLARE @STRINGLIST AS VARCHAR(1000)='apple,banana,lemon,kiwi,orange,coconut' DECLARE @SEPERATOR VARCHAR(1)=NULL select * from STRING_SPLIT(@STRINGLIST,@SEPERATOR)

We cannot assign NULL value to separator as a value.

In addition, when we use this function for numerical values, the result set will be in string data types. When we execute the following query, we can see all details and result in set table data type.

DROP TABLE IF EXISTS  TempStringSplit   SELECT VALUE INTO TempStringSplit FROM string_split('1,2,3',',') AS StrSplit   SELECT TABLE_NAME,COLUMN_NAME,COLUMN_DEFAULT,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH FROM INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='TempStringSplit'   DROP TABLE IF  EXISTS TempStringSplit

Now we will analyze the following query execution plan with ApexSQL Plan.

DROP TABLE IF EXISTS  TempNumerical GO CREATE TABLE TempNumerical (NumberId INT) INSERT INTO Numerical VALUES ( 1),(2),(3),(5) GO SELECT * FROM Numerical INNER JOIN string_split('1,2,3',',') AS StrSplit ON Numerical.NumberId = StrSplit.value

In the select operator, you are seeing a warning sign and now find out the details about this warning.

The reason for this warning is that we tried to join integer data type to varchar data type, so this type of usage causes implicit conversion. Implicit conversions affect the performance query.

MySQL Query GROUP BY day / month / year

 MySQL Query GROUP BY day / month / year

GROUP BY YEAR(record_date), MONTH(record_date)

 

How to edit pdf metadata from command line?

How to edit pdf metadata from command line?

Give exiftool a try; it is available from the package libimage-exiftool-perl in the repositories.

As an example, If you have a pdf file called drawing.pdf and you want to update its metadata, use the utility, exiftool, in this way:

exiftool -Title="This is the Title" -Author="Happy Man" -Subject="PDF Metadata" drawing.pdf

For some reason the Subject entered ends up in the keywords field of the metadata in the pdf file. not a problem in some cases, even desirable, however, this may be problematic: evince and the nautilus metadata previewer do not show this, but Adobe Acrobat viewer and PDF-XChange viewer do.

The program will create a backup of the original file if you do not use the -overwrite_original switch. This means a duplicate will exist in the folder where the updated pdf is. From the example above, a file named drawing.pdf_original will be created.

Use the overwrite switch at your own risk. My suggestion is not to use it and script something to move this file to a better location just in case.

 

https://exiftool.org/gui/

How to Get Data in Descending order using group by in mysql

How to Get Data in Descending order using group by in mysql

SELECT player_messages.*

FROM     player_messages
WHERE    (receiver_user_id, sender_user_id, insertdate) IN (
           SELECT   receiver_user_id, sender_user_id, MAX(insertdate)
           FROM     player_messages
           WHERE    receiver_user_id='33'
           GROUP BY receiver_user_id, sender_user_id)
ORDER BY player_messages.insertdate DESC

 

Format date and time in a Windows batch script

 Format date and time in a Windows batch script

 

set hour=%time:~0,2%
if "%hour:~0,1%" == " " set hour=0%hour:~1,1%
echo hour=%hour%
set min=%time:~3,2%
if "%min:~0,1%" == " " set min=0%min:~1,1%
echo min=%min%
set secs=%time:~6,2%
if "%secs:~0,1%" == " " set secs=0%secs:~1,1%
echo secs=%secs%

set year=%date:~-4%
echo year=%year%

:: On WIN2008R2 e.g. I needed to make your 'set month=%date:~3,2%' like below ::otherwise 00 appears for MONTH

set month=%date:~4,2%
if "%month:~0,1%" == " " set month=0%month:~1,1%
echo month=%month%
set day=%date:~0,2%
if "%day:~0,1%" == " " set day=0%day:~1,1%
echo day=%day%

set datetimef=%year%%month%%day%_%hour%%min%%secs%

echo datetimef=%datetimef%

Powershell Start-Sleep

https://learn.microsoft.com/en-us/powershell/module/microsoft.powershell.utility/start-sleep?view=powershell-7.2&viewFallbackFrom=powershell-7.1 

Start-Sleep

• Reference

Module:

Microsoft.PowerShell.Utility

Suspends the activity in a script or session for the specified period of time.

Syntax

PowerShell

Start-Sleep
     [-Seconds] <Double>
     [<CommonParameters>]

PowerShell

Start-Sleep
     -Milliseconds <Int32>
     [<CommonParameters>]

Description

The Start-Sleep cmdlet suspends the activity in a script or session for the specified period of time. You can use it for many tasks, such as waiting for an operation to complete or pausing before repeating an operation.

Examples

Example 1: Pause execution for 1.5 seconds

In this example, the execution of commands pauses for one and one-half seconds.

PowerShell

Start-Sleep -Seconds 1.5

Example 2: Pause execution at the command line

This example shows that execution is paused for 5 seconds when run from the command line.

PowerShell

PS> Get-Date; Start-Sleep -Seconds 5; Get-Date

Friday, May 13, 2022 9:38:15 AM
Friday, May 13, 2022 9:38:20 AM

PowerShell cannot execute the second Get-Date command until the sleep timer expires.

Parameters

-Milliseconds

Specifies how long the resource sleeps in milliseconds. The parameter can be abbreviated as m.

Type:	Int32
Aliases:	ms
Position:	Named
Default value:	None
Accept pipeline input:	True
Accept wildcard characters:	False

-Seconds

Specifies how long the resource sleeps in seconds. You can omit the parameter name or you can abbreviate it as s. Beginning in PowerShell 6.2.0, this parameter now accepts fractional values.

Type:	Double
Position:	0
Default value:	None
Accept pipeline input:	True
Accept wildcard characters:	False

Inputs

Int32

You can pipe the number of seconds to Start-Sleep.

Outputs

None

This cmdlet does not return any output.

Notes

• You can also refer to Start-Sleep by its built-in alias, sleep. For more information, see about_Aliases.
• Ctrl+C breaks out of Start-Sleep.
• Ctrl+C does not break out of [Threading.Thread]::Sleep. For more information, see Thread.Sleep Method.