CODEWITHSUNDEEP
laravellaravel-5.1
Morteza Ziyaeimehr
Morteza Ziyaeimehr

Reputation: 2117

UNION DISTINCT in laravel query builder

When i use $query2->union($query1) this is the result sql in laravel:

query one ...
union
query two ...

How can i have this query:

query one ...
union distinct
query two ...

In laravel documentation i saw only union() and unionAll() methods.

Upvotes: 2

Views: 4248

Answers (1)

dKen
dKen

Reputation: 3127

As per this answer, a UNION query by definition is a distinct query (assuming the columns in the two results are the same). A UNION ALL returns duplicates if they exist.

So, in Laravel 5.1 (at least), running a union as per the example in the documentation is, by default, distinct:

$first = DB::table('users')
    ->whereNull('first_name');

$users = DB::table('users')
    ->whereNull('last_name')
    ->union($first)
    ->get();

I'm not going to repeat what has been said well in that answer regarding performance (it's worth reading) but in summary a DISTINCT is slower than a DISTINCT ALL.

Upvotes: 1

Related Questions