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

learn more… | top users | synonyms

7
votes
0answers
321 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
13 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
36 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
233 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
24 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
18 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
55 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
48 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
32 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
64 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
199 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
36 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
49 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
437 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
187 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
344 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
103 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
391 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
123 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
195 views

SELECT, PROJECT, CARTESIAN PRODUCT and JOIN

I have the following tables; DOG +----------------------------------------------+ | Name Type Age Kennel_name Owner_name | +----------------------------------------------+ ...
1
vote
0answers
827 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
175 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
229 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
522 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
103 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
890 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
14 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
15 views

get minimum from result with group by mysql

i have table it store hierarchy data in mysql this table store stable relation but if each user les than 1000 buy removed and user User a lower level replace thia is my code and work fine, after qroup ...
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 | | ...
0
votes
0answers
39 views

Selected Child Categories based on parent categories from another table

I have two tables rules With three step hierarchy: |id | name | parent | |---|-------|--------| | 1 | A | 0 | | 2 | B | 0 | | 3 | A(1) | 1 | | 4 | A(2) ...
0
votes
0answers
33 views

Updating rows in multiple different tables using JOIN

Following is my MySQL SELECT query and it works. SELECT candidates.CandidateName, candidates.CandidateVotes, candidates.Party, candidates.win, candidates.NameOrder, ...
0
votes
0answers
43 views

How to give a prefix to all columns of a JOINed table in MySQL?

I have a data schema like this: car id type -> ENUM('Tesla', 'AstonMartin', 'Bmw') common_attribute car_tesla car_id specific_attribute_a specific_attribute_b ...
0
votes
0answers
41 views

Query the database for particular value in a column

I have 2 tables which look like this: Table_1: Contains Item Details ------------------------------------------------------ Item_ID | Location | Arrival_Date | Postage_Date | ... -------------------...
0
votes
0answers
37 views

SQL Joining across multiple tables

I'm trying to query across multiple queries at once, it is fairly easy to run queries one after the other but that is not what I should be doing in this case. I have one free variable and the rest of ...
0
votes
0answers
28 views

Get records with specific group name from table A and see if any exist in table B, if it does then add matching records in Table B to group

Let me start off by saying I am not a DB admin or a DB pro, I work with SQL as a php developer but that's about it. So I hope I can explain this properly and I hope one of you DB guys/gals can help. ...
0
votes
0answers
26 views

How to use COUNT() properly with a 3 table join

I have a database where I have driver details, their assigned routes and their stop locations. For each driver on the database I need to find the location (or locations, as it could be more than one) ...
0
votes
0answers
12 views

Schema of equijoin in relational algebra

Why does equijoin return a schema consisting of all attributes from both relations (including intersection) when equality between them have already been established? That is, we know that for any ...