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:
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 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:
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
- Starting a blog? I use WordPress for the Digital Owl’s Prose blog. Let’s both save money on the plans offered. 💸
- Get your brand, product, or service the attention it deserves with affordable classified ad placement in the OpenLampTech newsletter.
- 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 and they provide free SSL.
- 🔒5 Truths I’ve Come To Realize As a Self-taught Developer
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!