Select Where Not In with Select

In this article, we will explore how to use SELECT IN and WHERE NOT EXISTS in SQL Server, as well as equivalent approaches in Laravel. We’ll dive into the details of these queries and provide examples to illustrate their usage.

SQL Server: Using SELECT IN

The SELECT IN statement is used to select rows from a table where the column values are present in a list of values. In our case, we want to select the duration for employees who have not been part of a band for more than a year.

Here’s an example query:

SELECT CAST(DATEDIFF(DAY, MIN([fbh].FBH_DATA_INICIAL), 
                   CASE WHEN MAX([fbh].FBH_DATA_Final) = '9999-12-31' THEN CAST(GETDATE() AS DATE) ELSE MAX([fbh].FBH_DATA_Final) END)AS FLOAT) / CAST(365 AS FLOAT) AS duration
FROM [funcionario] AS [f]
INNER JOIN [funcionario_banda_historico] AS [fbh] ON [f].[FUN_ID] = [fbh].[FUN_ID]
AND [f].[FUN_BANDA] = [fbh].[FUN_BANDA]
WHERE NOT EXISTS (
    SELECT 1
    FROM funcionario_banda_historico t1
    WHERE t1.FUN_ID = [fbh].FUN_ID
      AND t1.FBH_DATA_INICIAL > [fbh].FBH_DATA_Final
      AND t1.FUN_BANDA <> [fbh].FUN_BANDA )
  AND [f].[FUN_ID] = '9999999'
GROUP BY f.fun_id,
         [f].[FUN_BANDA]

As we can see, this query is quite complex. The NOT EXISTS clause is used to filter out employees who have been part of a band for more than a year.

However, in our case, using SELECT IN would be even more complicated:

SELECT *
FROM [funcionario] AS [f]
INNER JOIN [funcionario_banda_historico] AS [fbh] ON [f].[FUN_ID] = [fbh].[FUN_ID]
AND [f].[FUN_BANDA] = [fbh].[FUN_BANDA]
WHERE [f].[FUN_ID] IN (
    SELECT FUN_ID
    FROM funcionario_banda_historico t1
    WHERE t1.FBH_DATA_INICIAL > [fbh].FBH_DATA_Final
      AND t1.FUN_BANDA <> [fbh].FUN_BANDA )
AND [f].[FUN_ID] = '9999999'
GROUP BY f.fun_id,
         [f].[FUN_BANDA]

This query is much more complicated and harder to read.

SQL Server: Using WHERE NOT EXISTS

As we can see, using NOT EXISTS is a better approach than using SELECT IN. However, there’s another way to achieve the same result using WHERE NOT IN.

Here’s an example:

SELECT CAST(DATEDIFF(DAY, MIN([fbh].FBH_DATA_INICIAL), 
                   CASE WHEN MAX([fbh].FBH_DATA_Final) = '9999-12-31' THEN CAST(GETDATE() AS DATE) ELSE MAX([fbh].FBH_DATA_Final) END)AS FLOAT) / CAST(365 AS FLOAT) AS duration
FROM [funcionario] AS [f]
INNER JOIN [funcionario_banda_historico] AS [fbh] ON [f].[FUN_ID] = [fbh].[FUN_ID]
AND [f].[FUN_BANDA] = [fbh].[FUN_BANDA]
WHERE NOT EXISTS (
    SELECT 1
    FROM funcionario_banda_historico t1
    WHERE t1.FUN_ID = [fbh].FUN_ID
      AND t1.FBH_DATA_INICIAL > [fbh].FBH_DATA_Final
      AND t1.FUN_BANDA <> [fbh].FUN_BANDA )
  AND [f].[FUN_ID] = '9999999'
GROUP BY f.fun_id,
         [f].[FUN_BANDA]

This query is the same as before.

However, we can also use WHERE NOT IN to achieve the same result:

SELECT CAST(DATEDIFF(DAY, MIN([fbh].FBH_DATA_INICIAL), 
                   CASE WHEN MAX([fbh].FBH_DATA_Final) = '9999-12-31' THEN CAST(GETDATE() AS DATE) ELSE MAX([fbh].FBH_DATA_Final) END)AS FLOAT) / CAST(365 AS FLOAT) AS duration
FROM [funcionario] AS [f]
INNER JOIN [funcionario_banda_historico] AS [fbh] ON [f].[FUN_ID] = [fbh].[FUN_ID]
AND [f].[FUN_BANDA] = [fbh].[FUN_BANDA]
WHERE NOT ([f].[FUN_ID], [fbh].FBH_DATA_INICIAL, [fbh].FBH_DATA_Final) IN (
    SELECT FUN_ID, FBH_DATA_INICIAL, FBH_DATA_Final
    FROM funcionario_banda_historico t1
    WHERE t1.FBH_DATA_INICIAL > [fbh].FBH_DATA_Final
      AND t1.FUN_BANDA <> [fbh].FUN_BANDA )
  AND [f].[FUN_ID] = '9999999'
GROUP BY f.fun_id,
         [f].[FUN_BANDA]

This query is much more complicated and harder to read than the NOT EXISTS approach.

Laravel: Using SELECT IN

In Laravel, we can use the SELECT IN statement to achieve the same result:

protected function getYearsInBand($userId) {
    $fbh = DB::table('funcionario as f')
            ->join('funcionario_banda_historico as fbh', function($join) {
                $join->on('f.FUN_ID', '=', 'fbh.FUN_ID');
                $join->on('f.FUN_BANDA', '=', 'fbh.FUN_BANDA');
            })
            ->selectRaw('cast(datediff(day,min([fbh].FBH_DATA_INICIAL),case when max([fbh].FBH_DATA_Final) = "9999-12-31" then cast(getdate() as date) else max([fbh].FBH_DATA_Final) end)AS FLOAT)/CAST(365 AS FLOAT) AS duration')
            ->where('f.FUN_ID', $userId)
            ->whereRaw('t1.FUN_ID = fbh.FUN_ID')
            ->whereRaw('t1.FBH_DATA_INICIAL > fbh.FBH_DATA_Final')
            ->whereRaw('t1.FUN_BANDA <> fbh.FUN_BANDA')
            ->groupBy('f.FUN_ID', 'f.FUN_BANDA')
            ->first();

    if (!$fbh) {
        Log::debug('funcionario_banda_historico not found'); // Grava no log
        return 0;
    }

    return $fbh->duration;
}

This query is much more readable and maintainable than the SQL Server version.

Laravel: Using WHERE NOT EXISTS

In Laravel, we can use the WHERE NOT EXISTS statement to achieve the same result:

protected function getYearsInBand($userId) {
    $fbh = DB::table('funcionario as f')
            ->join('funcionario_banda_historico as fbh', function($join) {
                $join->on('f.FUN_ID', '=', 'fbh.FUN_ID');
                $join->on('f.FUN_BANDA', '=', 'fbh.FUN_BANDA');
            })
            ->selectRaw('cast(datediff(day,min([fbh].FBH_DATA_INICIAL),case when max([fbh].FBH_DATA_Final) = "9999-12-31" then cast(getdate() as date) else max([fbh].FBH_DATA_Final) end)AS FLOAT)/CAST(365 AS FLOAT) AS duration')
            ->where('f.FUN_ID', $userId)
            ->whereRaw('t1.FUN_ID = fbh.FUN_ID')
            ->whereRaw('t1.FBH_DATA_INICIAL > fbh.FBH_DATA_FINAL')
            ->whereRaw('t1.FUN_BANDA <> fbh.FUN_BANDA')
            ->groupBy('f.FUN_ID', 'f.FUN_BANDA')
            ->first();

    if (!$fbh) {
        Log::debug('funcionario_banda_historico not found'); // Grava no log
        return 0;
    }

    return $fbh->duration;
}

This query is much more readable and maintainable than the SQL Server version.

Laravel: Using WHERE NOT IN

In Laravel, we can use the WHERE NOT IN statement to achieve the same result:

protected function getYearsInBand($userId) {
    $fbh = DB::table('funcionario as f')
            ->join('funcionario_banda_historico as fbh', function($join) {
                $join->on('f.FUN_ID', '=', 'fbh.FUN_ID');
                $join->on('f.FUN_BANDA', '=', 'fbh.FUN_BANDA');
            })
            ->selectRaw('cast(datediff(day,min([fbh].FBH_DATA_INICIAL),case when max([fbh].FBH_DATA_Final) = "9999-12-31" then cast(getdate() as date) else max([fbh].FBH_DATA_Final) end)AS FLOAT)/CAST(365 AS FLOAT) AS duration')
            ->where('f.FUN_ID', $userId)
            ->whereRaw('t1.FUN_ID <> fbh.FUN_ID')
            ->whereRaw('t1.FBH_DATA_INICIAL <> fbh.FBH_DATA_INICIAL')
            ->whereRaw('t1.FUN_BANDA <> fbh.FUN_BANDA')
            ->groupBy('f.FUN_ID', 'f.FUN_BANDA')
            ->first();

    if (!$fbh) {
        Log::debug('funcionario_banda_historico not found'); // Grava no log
        return 0;
    }

    return $fbh->duration;
}

This query is much more complicated and harder to read than the WHERE NOT EXISTS approach.

Conclusion

In this article, we’ve explored how to use SELECT IN, WHERE NOT EXISTS, and WHERE NOT IN in SQL Server and Laravel. We’ve seen that using NOT EXISTS is often the most readable and maintainable approach, while WHERE NOT IN can be more complicated.

We’ve also seen how to achieve the same result in both languages by joining two tables on multiple conditions and selecting the desired columns.

Finally, we hope this article has provided you with a better understanding of how to use these SQL Server and Laravel features to achieve your data retrieval goals.

Last modified on 2024-09-26