topic Re: Does "WHERE" SQL clause have the same row limitation as "INNER JOIN"? in Splunk Search

Does "WHERE" SQL clause have the same row limitation as "INNER JOIN"?

LearningGuy — Tue, 05 Sep 2023 15:10:02 GMT

Hello,
Does "WHERE" SQL clause have the same row limitation as "INNER JOIN"?
Does "WHERE" and "INNER JOIN" have the same function and result?
Thank you for your help

For example:

| dbxquery connection=DBtest
query="SELECT a.name, b.department
FROM tableEmployee a
INNER JOIN tableCompany b
ON a.id = b.emp_id

| dbxquery connection=DBtest
query="SELECT a.name, b.department
FROM tableEmployee a, tableCompany b
WHERE a.id = b.emp_id

Re: Does "WHERE" SQL clause have the same row limitation as "INNER JOIN"?

richgalloway — Tue, 05 Sep 2023 17:18:14 GMT

AFAIK, the dbxquery command doesn't care what the query does. It's up to the DB itself to interpret the SQL and decide how many rows to return. Splunk's limit will be the same either way.

Re: Does "WHERE" SQL clause have the same row limitation as "INNER JOIN"?

LearningGuy — Tue, 05 Sep 2023 17:47:28 GMT

Hello,

According to the following documentation, the limit is 50,000 rows.
I am wondering if WHERE clause can fix this problem or it's the same.
Can you take a look and see if it's accurate? Thanks
https://community.splunk.com/t5/Splunk-Search/How-to-join-large-tables-with-more-than-50-000-rows-in-Splunk/m-p/152136

https://docs.splunk.com/Documentation/SCS/current/SearchReference/JoinCommandOverview

Re: Does "WHERE" SQL clause have the same row limitation as "INNER JOIN"?

richgalloway — Tue, 05 Sep 2023 18:12:26 GMT

That documentation doesn't apply in this case. It refers to the join command, which is not being used in the sample query. The query uses the SQL JOIN operator, which is not the same as the SPL join command.

As I mentioned in my earlier response, everything in the dbxquery command is processed by the remote database, not by Splunk. Splunk only sees the results of the query.