Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have below query:

select entity_id from table1 exc 
UNION 
select entity_id from tabl2 inc

On execution, I got 24419 rows.

When I execute select entity_id from table1 exc I got 16235 rows and on the execution of select entity_id from tabl2 inc I got 35943 rows.

Below is the explain result of above query:

*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: exc
         type: index
possible_keys: NULL
          key: IDX_ENTITY_ID
      key_len: 4
          ref: NULL
         rows: 15603
        Extra: Using index
*************************** 2. row ***************************
           id: 2
  select_type: UNION
        table: inc
         type: index
possible_keys: NULL
          key: IDX_ENTITY_ID
      key_len: 4
          ref: NULL
         rows: 32697
        Extra: Using index
*************************** 3. row ***************************
           id: NULL
  select_type: UNION RESULT
        table: <union1,2>
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
        Extra:
3 rows in set (0.00 sec)

Table structure of table1

Create Table: CREATE TABLE `table1` (
  `entity_id` int(11) NOT NULL DEFAULT '0',
  `rule_id` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`entity_id`,`rule_id`),
  KEY `IDX_ENTITY_ID` (`entity_id`),
  KEY `idx_rule_id_jos_tag_rules_content_exclude` (`rule_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC

Table structure of table2

Create Table: CREATE TABLE `table2` (
  `entity_id` int(11) NOT NULL DEFAULT '0',
  `rule_id` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`entity_id`,`rule_id`),
  KEY `IDX_ENTITY_ID` (`entity_id`),
  KEY `idx_rule_id_jos_tag_rules_content_include` (`rule_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC

If we see on algebric calculation behind Union operation then it should be max(No(a)+no(b)). Now, I am unable to understand behind above result. How MySQL perform Union operation?

share|improve this question
    
What is the question? Why you get 24419 and not 16235+35943 rows in the result? –  ypercube Jan 30 at 13:44
4  
UNION eliminates the duplicates,use UNION ALL to include them. –  Mihai Jan 30 at 14:11
    
@ypercube: yes, this is my question. –  ursitesion Jan 30 at 15:46

1 Answer 1

union returns distinct results. try UNION ALL

select entity_id from table1 exc UNION ALL select entity_id from tabl2 inc

share|improve this answer
    
Union in MySQL different than Union in Set theory of Math? –  ursitesion Jan 31 at 10:08

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.