CodeIgniter 4 Query Builder union() and unionAll() methods

UNION and UNION ALL set operators return the combined rows from 1 or more SELECT queries. CodeIgniter 4 Query Builder now supports UNION and UNION ALL queries with the $builder->union() and $builder->unionAll() methods respectively. Learn how to create these types of queries in this article.

This article and many to follow, are part of a series of articles I am writing and sharing as I learn new concepts when I learn them. I think of it as a duty in sharing the things I learn as a self-taught developer. We all need to support one another as we continue to learn and grow. #buildinpublic #learninpublic #indiehackers #developer

The Newsletter for PHP and MySQL Developers

Receive a copy of my ebook, “10 MySQL Tips For Everyone”, absolutely free when you subscribe to the OpenLampTech newsletter.

Data setup and housekeeping

I’m using 2 simple tables with minimal data for the example queries, a customer and employee table:

Sample employee table data
Sample customer table data

I also have these 2 protected properties in the Model class I am working with:

protected $db;
protected $builder;

I’ll be logging (not shown) all of the executed queries using the $db->getLastQuery() method. I have written in-depth about $db->getLastQuery() in the past so I won’t go into any details about it here.


CodeIgniter 4 Query Builder $builder->union() method

Perhaps one of the classic database practice questions with these 2 tables could be a requirement of: “Show me all the employees and customers in a single table.”

We can absolutely do that with a UNION by using the $builder->union() method.

codeigniter 4 query builder union

The call to $builder->union() produces the equivalent UNION query as shown in the MySQL code:

mysql union query

My observations: Right off the bat there are some interesting things here looking at the SQL statement executed:

  • Both of the SELECT statements are actually querying a Derived Table.
  • Each SELECT statement is automatically given an Alias with `uwrp0` being the main query and any subsequent SELECTs as part of the UNION having `uwrp+n`

Notice in the below associative array results that only 1 element is for the ‘John Doe’ record although there is a ‘John Doe’ row in both the customer and employee tables.

$builder->union() returns only distinct rows with no duplicates.

php associative array from codeigniter model

πŸ“° Get your brand, product, or service the attention it needs with affordable classified ad placement in the OpenLampTech newsletter. Thank you for your support!


CodeIgniter 4 Query Builder $builder->unionAll() method

In the event you need all rows returned – including duplicates – the $builder->unionAll() method does that:

codeigniter query builder unionall

We can see in the following MySQL code, that the UNION ALL set operator is used in the $builder->unionAll() method:

mysql union all query

Notice in the following associative array that both ‘John Doe’ rows from the employee and customer tables are included since $builder->unionAll() returns all rows:

php associative array from codeigniter 4 model

CodeIgniter 4 is continuously improving the framework with releases that enable you to craft powerful and staple queries using the Query Builder. Let me know if you have ported over or are using any $builder->union() and/or $builder->unionAll() methods in your Model queries in the comment below.

Additional Reading

Thank you for reading this post. Please share it with someone else who would enjoy it as well.




Josh Otwell has a passion to grow as a PHP Developer, SQL expert, and technical blogger/writer.

Disclaimer: The majority of examples in this post, are performed in a personal development/learning workstation environment and should not be considered production quality or ready. Your particular goals and needs may vary. Like always, just because you can do something doesn’t mean you should. My opinions are my own.

More ways I can help

Disclosure: Some of this blog post’s services and product links are affiliate links. At no additional cost to you, should you make a purchase by clicking through one of them, I will receive a commission.

The Newsletter for PHP and MySQL Developers

Receive a copy of my ebook, “10 MySQL Tips For Everyone”, absolutely free when you subscribe to the OpenLampTech newsletter.


πŸ“° Get your brand, product, or service the attention it needs with affordable classified ad placement in the OpenLampTech newsletter. Thank you for your support!


Hey thanks for commenting! Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.