Laravel whereIn and whereNotIn with subquery example. In this tutorial, you will learn how to use whereIn and whereNotIn subquery in laravel.
Sometimes you want to get nested data from database tables and some time exclude some nested data from DB table. So you can use whereIn and WhereNotIn subquery for that.
Laravel WhereIn SubQuery
The following examples queries fetch data from user table, which is available in the user_role table using whereIn subquery.
See the following examples:
Example 1: WhereIn SubQuery Using Query Builder
DB::table('users')->whereIn('id', function($query) {
$query->select('user_id')->from('role_user');
})->get();
Example 2: WhereIn SubQuery Using Model
User::whereIn('id', function($query) {
$query->select('user_id')->from('role_user');
})->get();
When you dump the above given whereIn subQueries you will get the following SQL query:
SELECT * FROM `users` WHERE `id` IN (
SELECT `user_id` FROM `role_user`
)
Laravel WhereNotIn SubQuery
The following examples queries fetch data from user table, which is not available in the user_role table by using whereNotIn subquery.
See the following examples:
Example 1: whereNotIn SubQuery Using Query Builder:
DB::table('users')->whereNotIn('id', function($query) {
$query->select('user_id')->from('role_user');
})->get();
Example 2: WhereNotIn SubQuery Using Model
User::whereNotIn('id', function($query) {
$query->select('user_id')->from('role_user');
})->get();
When you dump the above given whereIn subQueries you will get the following SQL query:
SELECT * FROM `users` WHERE `id` NOT IN (
SELECT `user_id` FROM `role_user`
)
Conclusion
In this laravel where in and where not in subquery example tutorial, you have learned how to implement subquery using laravel whereIn and wherenotin eloquent methods in laravel.
