An SQL join clause combines records from two or more tables or views.

learn more… | top users | synonyms

7
votes
0answers
348 views

Performance inner join vs left join involving parallelism

I have a query which takes about 8 hours to finish. Simplified, the query looks something like this: SELECT [Some attributes, inconsequential] FROM RBM.Financial_Events RFE INNER JOIN ...
3
votes
0answers
51 views

Do Large Hash Joins Contribute to Stolen Pages?

On a SQL Server 2014 SP1 Enterprise Instance, I'm performing an outer join between two partitioned tables on a numeric(16,0) + char(1) composite key. 100 GB is the max server memory setting on a 128 ...
2
votes
0answers
15 views

How does the use of derived tables speed up sorting?

In the article 45 Ways To Speed Up Firebird Database, it states that Use derived tables to optimize SELECT with ORDER BY/GROUP BY Another way to optimize SQL query with sorting is to ...
2
votes
0answers
38 views

How to avoid row locks on both tables joined in an update?

I have little knowledge about MySQL internals, but I'm experiencing that in a query like: UPDATE t1 JOIN t2 ON t1.id = t2.id SET t1.a = x, t1.b = y, [, ...] WHERE [ ... ] rows from both t1 and ...
2
votes
0answers
18 views

Join integration query involving multiple associations

I'd like some assistance in fixing this query. Let me explain the background on what it currently does, and then what I need to integrate. First, here are my current tables. users (performers) table ...
2
votes
0answers
31 views

Query optimization - contact should be extracted at most once for a particular customer

I have created a database for a telemarketing company Scenario is, the telemarketing company is selling contacts to the customers. Customers log on to the website and search for the contacts for ...
2
votes
0answers
236 views

How to index for a group by with columns from different tables?

Is it possible to create an index from columns of 2 different tables? If not, how do we optimize a query that does a group by on columns of 2 different tables? For example: the following ...
1
vote
0answers
16 views

Join Tables of Varying Criteria in Order of Priority

I am trying to join two tables based on varying criteria in order of priority. The situation I have inherited a clinical database which has inherently bad data quality from years of poor input and ...
1
vote
0answers
32 views

How similar is using a secondary index in InnoDB to using a second table joining 'into' the PK?

Since InnoDB secondary indexes are separate B-trees with the key as the sort order and the PK as the 'value' stored, I assumed that reading into the main table by the secondary index was a lot like ...
1
vote
0answers
41 views

get max item in sub query join mysql

i have a table it contain buy of each user , other table contain a star ranking According to buys all user, my scenario very sample i create it and show in sqlfiddle . example if in table buy have ...
1
vote
0answers
21 views

Primary key index not used when using inner join with jsonb_array_elements subquery on Postgresql 9.5

I have two tables: hub and huboperator hub table is defined as CREATE TABLE public.hub ( parent_hub_id integer, ancestors jsonb, descendants jsonb, name character varying(1024), id integer ...
1
vote
0answers
58 views

Simplify Month Over Month % Change, 4 Months Moving Average %

invoice created_at | amount | status | ------------+--------+--------- 2016-06-01 | 50 | 2 | 2016-05-30 | 150 | 2 | 2016-04-29 | 32 | 2 | 2016-03-28 | ...
1
vote
0answers
31 views

Groupwise max then join

I have a votes table that has many subitems. I have a subitems action table that records what action was performed by which user on which group, on which subitem, on which vote Users can either cast/...
1
vote
0answers
52 views

Inner join on array columns from 3 tables

I have 2 tables that are related between them using integer data which is stored in arrays. Data table telemetria_data.historico_354677050164513 CREATE TABLE historico_354677050164513 ( imei ...
1
vote
0answers
33 views

MySQL Update column with case on joined tables

I have 2 tables and I want to update table 1 using a case check on table 2: update cms.Clients c #c_id unique join cms.ClientDomains d #c_id not unique on c.c_id = d.c_id set c.country = case ...
1
vote
0answers
40 views

Fetch items where related rows has different values

I have table in MySQL called attributes, and other table, attribute_values, one attribute can have multiple values, values identified by context attribute and has value_c attribute wich contain value. ...
1
vote
0answers
21 views

Why does SemiJoin node receive so bad statistic even after runnning ANALYZE

PosgtreSQL 9.4 I have two tables: CREATE TABLE home_transactions( id serial NOT NULL, transaction_id integer ); CREATE TABLE transactions( id serial NOT NULL, user_id, amount numeric(13,4) NOT NULL )...
1
vote
0answers
65 views

Whats the difference between the following queries? Getting the top 3 salaries for different department

I am practicing SQL and came across this issue. I am trying to find the top 3 salaries for each department. The table structure is basic with one employee table with columns (Id,name,Salary,...
1
vote
0answers
48 views

3 tables joins performance issue

DB: Postgres 9.4 schema: table a: id, score table ab: a_id, b_id, b_type table b: id, name, type I am trying to get top 10 result in the following format: name, count(group by name), average(...
1
vote
0answers
206 views

Update row value with a sub-select join

I am trying to update 1 column in all rows in a table to have an appropriate count value. Sadly, the SELECT query I am using won't let me join on a field in update table. The query looks like this (...
1
vote
0answers
48 views

Efficient structure for querying membership in many-to-many relationship where tableA has ONLY the indicated members from tableB?

I'm working on an application with a PostgreSQL back end. There is a many-to-many relationship between items in one table and tags in another table. The specs require that the application be able to ...
1
vote
0answers
38 views

Dealing with joins in persistent key-value stores

One promising emerging category of NoSQL databases is key-value stores. These include Redis (disk-backed), Riak, Aerospike and others. The problem I struggle with the most is the lack of joins. ...
1
vote
0answers
39 views

Type of relational/object-oriented database with improved joins for object graphs?

I would like to know if there is a specific name or term for the kind of database described below: I notice one of the biggest problems when working with relational databases and object trees/graphs ...
1
vote
0answers
50 views

Delete using join or using sub-query?

I need to delete rows from a table based on what is present in a temporary table. For me, both of these statements work : DELETE from main_table where `id` in (select `deletable_id` from ...
1
vote
0answers
447 views

Oracle 10.2.0.4 query hundreds times slower than the same query on Oracle 10.2.0.3

I have an issue with query on Oracle 11.2.0.4.0 (it takes forever to complete and CPU is at 100% all the time) so I was trying to change optimizer_features_enable to previous versions. It worked with ...
1
vote
0answers
1k views

How to display all the dates between multiple two dates in a table?

I want to display all the dates between two dates for a particular record And this is the table : ID Start_Date End_Date 1 2013-01-14 2013-01-18 2 2013-02-01 2013-02-04 and now i want to get all ...
1
vote
0answers
198 views

How to improve performance when joining against coalesce

In an effort to remove a caching layer from our application, I'm building a number of views to encapsulate the different ways a user can have access to a piece of content - one per "rule" of access. ...
1
vote
0answers
370 views

Performance hits with MySQL Left Joins and Order By

There are a lot of questions out there regarding this issue, but most answers seem to be very case-specific. I've tried to make this general so it'll help others too. The Issue We have a smallish ...
1
vote
0answers
106 views

Poor performance filtering mysql spatial join

First of all, sorry for the crossposting. I ask this question in stackoverflow and they sent me here I have a mysql table with spatial references (GEOGRAPHIC_FILTER), like cities, countries, natural ...
1
vote
0answers
403 views

Limit relation one to many or one to one

In a relationship one to many or one to one you can limit the relationship of the related table? For example, I have a table 'cliente' and another 'acct', each 'cliente' can have thousands (perhaps ...
1
vote
0answers
124 views

Join and aggregate result and insert blanks for missing appendants

Consider these two simplified tables: CREATE TABLE "sources" ( "id" serial NOT NULL, "name" text NOT NULL, CONSTRAINT "sources_pkey" PRIMARY KEY ("id") ); CREATE TABLE "values" ( "...
1
vote
0answers
200 views

SELECT, PROJECT, CARTESIAN PRODUCT and JOIN

I have the following tables; DOG +----------------------------------------------+ | Name Type Age Kennel_name Owner_name | +----------------------------------------------+ ...
1
vote
0answers
843 views

USING to join multiple tables with same field

I have 4 tables, each with a common field QuestionnaireId. I am wondering if there is any difference in output or performance difference between using USING on the field in each join or by specifying ...
1
vote
0answers
176 views

join performance, index vs. index+foreignkey

I'm using an orm framework that handles data (and its integrity) on top of mysql. So far I ignored foreign keys because the orm handles that just fine. I recently read that foreign keys can enhance ...
1
vote
0answers
233 views

Relation between lossy decomposition and Natural join?

I am confused with the lossy decomposition and natural join. Natural join(with common attribute): only respective common attribute for the resultant table is formed. Lossy: Natural join on tables with ...
1
vote
0answers
164 views

MySQL join query indexes

After recently discovering the MySQL feature to log queries that aren't using indexes, I've been chasing these down in a large app, looking for places to improve performance. There is at least one ...
1
vote
0answers
526 views

How to increase MySQL performance with joins

I have a MySQL database with InnoDB engine. Particularly one of my queries became very slow. The query is shown below: SELECT t1.`ARTREF1` FROM `lijnen` AS t3 INNER JOIN (`kortingen` AS t2, `...
1
vote
0answers
104 views

Can't get rid of filesort on joined query sorting on second table

For some reason, this query: EXPLAIN SELECT * FROM biz as b INNER JOIN listings as l ON b.id = l.biz_id WHERE l.pub_id = 14 AND b.cat_id=310 ORDER BY l.level DESC, l.random DESC LIMIT 5; says it ...
1
vote
0answers
45 views

parenthetic grouping of join clauses

What is the difference between these joins? a left join b left join c (a left join b) left join c a left join (b left join c) Does the grouping only affect the order of the joins? Will the query ...
1
vote
0answers
1k views

How can I join one to one to (many to one) without using group_concat

I have a table structure as follows: -- -- Table structure for table `emails` -- CREATE TABLE IF NOT EXISTS `emails` ( `ID` int(5) NOT NULL AUTO_INCREMENT, `email` varchar(255) COLLATE ...
1
vote
0answers
894 views

multiple child records in a single row by join

I have one master table (teacher) structured like teacherId Name Class 1 Praveen 10 2 John 9 and having a child table (student) structured like studentId ...
0
votes
0answers
29 views

Subquery or Inner Join?

I have this simple query that finds all transactions from accounts belonging to a specific login, with some extra conditions on the transactions. SELECT t.id, t.date, t.amount, t.description FROM ...
0
votes
0answers
42 views

Find Intermediate Bus Stop and Source and Destination Bus

I have a table which consists of 4 Columns: PKID, Bus_ID, Stop_Position and Stop_ID. Data: BUS_ROUTE_TABLE PKID BUS_ID STOP_POSITION STOP_ID ------------------------------- 1 1 1 ...
0
votes
0answers
17 views

Do I need to index JOINED columns even if they only appear in SELECT?

This is a reformatting of the question asked here: MySQL JOIN is fast, but selecting column is slow Do I need to add an index to EVERY column in a JOINED table if that column will appear in the ...
0
votes
0answers
25 views

MySQL JOIN is fast, but selecting column is slow

My logs table has half a million rows. Each row has an indexed log_level_id column pointing to table log_levels (with only 5 rows). On log_levels, id is an auto-increment primary key and label has ...
0
votes
0answers
16 views

sum price of childs in other table mysql

i have two table one store data child and parent hierarchy and other paths and descendant +----------+------------+-----------+ | userid | parent | price | +----------+------------+--------...
0
votes
0answers
21 views

Traditional SELECT with Multiple Joins or SELECT JSONB then multiple SELECT

I am writing a simple recipe book app based on node.js with sequilize as ORM and Postgresql 9.4 as DB. Recipe has multiple ingredients that can be measured with different units. I store recipe steps ...
0
votes
0answers
35 views

how to get the data from table

Before this query is run an activity name (e.g. ‘Art’) should be assigned to a variable. The query should then output a list of the children registered for the specified activity. The output should be ...
0
votes
0answers
25 views

Joining tables in Eloquent ORM based on condition to disallow duplicates

I have two tables I am looking to join data. This is a friendship table based on a recommendation I've seen on here. It's an AB BA relationship between friends. I want to join this with a profile ...
0
votes
0answers
26 views

counting and sorting join mysql

i have table like bellow it store of amount of buys of each user +--------+----------+ | userid | amount | +--------+----------+ | 1 | 1000000 | | 2 | 80000000 | | 3 | 7000000 | | ...