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.
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:
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.
The call to $builder->union() produces the equivalent UNION query as shown in the MySQL code:
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.
📰 Get your brand, product, or service the attention it deserves with affordable classified ad placement in the OpenLampTech newsletter. I appreciate 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:
We can see in the following MySQL code, that the UNION ALL set operator is used in the $builder->unionAll() method:
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:
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
- How to Retrieve MySQL Last Insert ID in CodeIgniter 4
- CodeIgniter 4 Query Parameter Binding with examples in MySQL
- CodeIgniter 4 Query Builder join() method explained
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
- Need hosting for your next web application or WordPress site? I highly recommend Hostinger and use them to host my niche bass fishing site. The service is second to none.
- 🔒5 Truths I’ve Come To Realize As a Self-taught Developer
- Desktop and mobile wallpapers, digital downloads, photography services, Shopify and WooCommerce customizations, and content writing – all in one E-commerce Shop. Find your next digital purchase today!
- Take your Laravel applications next level with Battle Ready Laravel by Ash Allen. Learn how to improve the performance, maintainability, and security of your Laravel projects in this e-book.
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 deserves with affordable classified ad placement in the OpenLampTech newsletter. I appreciate your support!