'What is the different between using "IN" vs using "=" inside a query?

I'd like to understand the best way to restrict items in a query. For example Query1 returns a list of ID's based of specific detail filters. I would like Query2 to return only items where Query2.ID is the same as Query1.ID.

Which Query2 Detail Filter is better? And why?

  • Query2.ID IN ( Query1.ID )
  • Query2.ID = Query1.ID

Note: Using an "=" filter requires Query2's "Cross product allowed" property to be set to "Allow".

cognoscognos-11


Solution 1:[1]

IN is usually used to compare against a list, while = compares to a single value.

Examples:

... WHERE id IN (1, 2, 3)

vs.

... WHERE id = 1

Some documentation regarding Postgresql, which might be useful and similar to the cognos notation.

Solution 2:[2]

SQL efficiency - [=] vs [in] vs [like] vs [matches]

it depends on the underlying SQL engine. In MS-SQL, for example (according to the query planner output), IN clauses are converted to =, so there would be no difference

Which is better for you will depend on the specific report request. = is for single values and IN is for multiple values. It's all about the prompts. If you are hard-coding (so, not using a parameter) a filter that looks for a single value, use =.

But that's not really what you're asking...

Query2.ID = Query1.ID

You need a join, not a filter.

Solution 3:[3]

Using Cognos, consider an inner join as an option

  • Removes the need to allow cross join
  • Query 1 can be a very small data set
  • Avoid row to row comparison

  1. In cognos, query explorer, from insertable objects/toolbox, drag JOIN below all of your queries (should create query 3)

  2. Next, put Query 1 and Query 2 into the available openings

  3. Click the join icon to the right of Query 3 Define cardinality from Query 1 to Query 2 as a 1.1 to 1.n (to achieve the filter effect). Click Okay

  4. Next, single click on the join (just before Query 3 to get focus to set properties) and set the property for filter type to "In". Examine the generated SQL

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 gd_silva
Solution 2 dougp
Solution 3 VAI Jason

Related Questions